Two Functions in SQLServer (can replace the cursor) 1. Because stored procedures cannot be used in update, some fields in the update table must be calculated. We often use the cursor method. Here we use the Function Method for implementation. Function section: Reference segment: CREATEFUNCTION [DBO]. [FUN_GETTIME] (@ TASKPHASEIDI
Two functions in SQL Server (can replace the cursor) 1. Because stored procedures cannot be used in update, some fields in the update table must be calculated. We often use the cursor method. Here we use the Function Method for implementation. Function section: Reference segment: CREATEFUNCTION [DBO]. [FUN_GETTIME] (@ TASKPHASEIDI
SQL ServerFunction(Can replace the cursor)
1. Because the stored procedure cannot be used in update, it must be calculated based on some fields in the update table. We usually use the cursor method.Function.
FunctionPart:
The following is a reference clip: Create function [DBO]. [FUN_GETTIME] (@ taskphaseid int) RETURNS FLOAT 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 |
CallFunctionStored Procedure Section
The following is a reference clip: 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 |