详细讲解SQL Server中“函数”的两种用法

　　
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