函數部分：
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
調用函數的存儲過程部分：
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
