Two uses of functions in SQL Server (which can be used in place of cursors)
1. Because the update can not use stored procedures, but to update the table based on some of the fields to be calculated. 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