Two uses of functions in SQL Server (which can be used in place of cursors)

server| Functions | cursors

Two uses of functions in SQL Server (which can be used in place of cursors)
1. Because the stored procedure cannot be used in update, it is calculated according to some fields of the Update table. We often use the method of the cursor, which is implemented using the method of the function.

Function section:
CREATE FUNCTION [DBO]. [Fun_gettime] (@TASKPHASEID INT)
RETURNS FLOAT as
BEGIN
DECLARE @TASKID INT,
@HOUR FLOAT,
@PERCENT FLOAT,
@RETURN FLOAT
IF @TASKPHASEID is NULL
BEGIN
Return (0.0)
End

SELECT @TASKID =taskid, @PERCENT =isnull (workpercent,0)/100
From Tabletaskphase
WHERE id= @TASKPHASEID

SELECT @HOUR =isnull (tasktime,0) from Tabletask
WHERE id= @TASKID

SET @RETURN = @HOUR * @PERCENT
Return (@RETURN)
End

The part of the stored procedure that calls the function
CREATE PROCEDURE [DBO]. [Proc_calcca]
@ROID INT
As
BEGIN
DECLARE @CA FLOAT

UPDATE Tablefmeca
SET
cvalue_m= ISNULL (moderate,0) *isnull (fmerate,0) *isnull (b.basfailurerate,0) *[dbo]. [Fun_gettime] (c.id)
From Tablefmeca, TableRelation b,tabletaskphase C
WHERE roid= @ROID and Taskphaseid=c.id and b.id= @ROID

SELECT @CA =sum (ISNULL (cvalue_m,0)) from Tablefmeca WHERE roid= @ROID

UPDATE tablerelation
SET criticality= @CA
WHERE id= @ROID
End
Go

2. We have to calculate the sum based on some records of a table, because we cannot store the median value, we usually use the method of the cursor to calculate. But sqlserver2000 support.
SUM ([All | DISTINCT] expression)

Expression

is a constant, column, or function, or any combination of arithmetic, bitwise, and string operators. So we can take advantage of this function.

Function section:

CREATE FUNCTION [DBO]. [Fun_rate] (@PARTID int, @ENID int, @SOURCEID int, @QUALITYID int, @COUNT int)

RETURNS FLOAT as
BEGIN
DECLARE @QXS float, @g float, @RATE float

IF (@ENID =null) or (@PARTID =null) or (@SOURCEID =null) or (@QUALITYID =null)
BEGIN
Return (0.0)
End

SELECT @QXS = ISNULL (xs,0) from tablequality WHERE id= @QUALITYID
SELECT @g=isnull (frate_g,0) from Tablefailurerate
WHERE (subkindid= @PARTID) and ( enid= @ENID) and ( datasourceid= @SOURCEID) and (ISNULL ( mincount,0) <=isnull (@COUNT, 0)) and (ISNULL (maxcount,0) >=isnull (@COUNT, 0)))
OR (ISNULL (@COUNT, 0) >isnull (maxcount,0))

SET @RATE =isnull (@QXS *@g,0)
Return (@RATE)
End

The part of the stored procedure that invokes the function:

CREATE PROC Proc_faultrate

@PARTID integer, @QUALITYID integer, @SOURCEID integer, @COUNT integer, @ROID int, @GRADE int, @RATE float=0 Outputas
BEGIN
DECLARE
@TASKID INT
SET @RATE =0.0

Select @TASKID =isnull (taskproid,-1) from TableRelation where id= (select PID from tablerelation where id= @ROID)

IF (@TASKID =-1) OR (@GRADE =1) BEGIN
SET @RATE =0
Return
End

SELECT @RATE =sum ([dbo].[ Fun_rate] (@PARTID, ENID, @SOURCEID, @QUALITYID, @COUNT) *isnull (workpercent,0)/100.0)

From Tabletaskphase
WHERE taskid= @TASKID
End
Go

function can also return tables, and so on, I hope that we discuss the magical functions of SQL Server.