Functions in SQL Server)

Functions in SQL Server)
1. Because the stored procedure cannot be used in update, it must be calculated based on some fields in the update table. We often use the cursor method. Here we use the function method for implementation.

Function section:
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
Stored procedure of calling a 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 need to calculate the sum based on some records of a table. Because the median value cannot be stored, we usually use the cursor method for calculation. But sqlserver2000 supports
SUM ([ALL | DISTINCT] expression)
Expression
It is a constant, column, or function, or any combination of arithmetic, bitwise, string, and other operators. Therefore, we can use this function.
Function section:
Create function [DBO]. [FUN_RATE] (@ partid int, @ enid int, @ sourceid int, @ qualityid int, @ count int)
RETURNS FLOAT
BEGIN
DECLARE @ qxs float, @ g float, @ RATE FLOAT