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
WHEREID = @ TASKPHASEID

SELECT @ HOUR = ISNULL (TASKTIME, 0) FROM TABLETASK
WHEREID = @ 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
WHEREROID = @ ROIDAnd taskphaseid = C. ID ANDB. ID = @ ROID

SELECT @ CA = SUM (ISNULL (Cvalue_M, 0) FROM TABLEFMECA WHEREROID = @ ROID

UPDATE TABLERELATION
SETCRITICALITY = @ CA
WHEREID = @ 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

IF (@ ENID = NULL) OR (@ PARTID = NULL) OR (@ SOURCEID = NULL) OR (@ QUALITYID = NULL)
BEGIN
RETURN (0.0)
END

SELECT @ QXS = ISNULL (XS, 0) FROM TABLEQUALITY WHEREID = @ QUALITYID
SELECT @ G = ISNULL (FRATE_G, 0) FROM TABLEFAILURERATE
WHERE (SUBKINDID = @ PARTID) AND (ENID = @ ENID) AND (Performanceid = @ 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

Stored Procedure of calling a 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 WHEREID = @ 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
WHERETASKID = @ TASKID
END
GO

Functions can also return tables and so on. We hope you can discuss the advantages and disadvantages of functions in sqlserver.