SQL Server 函數與預存程序實戰範例：故障率計算邏輯封裝

使用者定義函數：FUN_RATE

計算故障率核心邏輯，接收零件、設備、資料來源、品質等級、數量五個參數，回傳浮點數率值。

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

    -- 參數檢核：任一關鍵參數為 NULL 直接回傳 0
    IF (@ENID IS NULL) OR (@PARTID IS NULL) OR (@SOURCEID IS NULL) OR (@QUALITYID IS NULL)
        RETURN 0.0

    -- 取得品質係數
    SELECT @QXS = ISNULL(XS, 0) FROM TABLEQUALITY WHERE ID = @QUALITYID

    -- 取得基礎故障率 G，依零件分類、設備、資料來源、數量區間比對
    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

呼叫函數的預存程序：PROC_FAULTRATE

彙總某任務階段下所有階段的故障率加權平均，輸出參數 @RATE。

CREATE PROC PROC_FAULTRATE
    @PARTID INTEGER,
    @QUALITYID INTEGER,
    @SOURCEID INTEGER,
    @COUNT INTEGER,
    @ROID INT,
    @GRADE INT,
    @RATE FLOAT = 0 OUTPUT
AS
BEGIN
    DECLARE @TASKID INT
    SET @RATE = 0.0

    -- 取得上層任務 ID
    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

    -- 加權彙總：呼叫 FUN_RATE 並依工作比重加權
    SELECT @RATE = SUM( [DBO].[FUN_RATE](@PARTID, ENID, @SOURCEID, @QUALITYID, @COUNT)
                        * ISNULL(WORKPERCENT, 0) / 100.0 )
    FROM TABLETASKPHASE
    WHERE TASKID = @TASKID
END
GO

設計重點

• 參數防禦：函數開頭即檢核關鍵參數 IS NULL，避免後續查詢錯誤。
• ISNULL 保護：所有查詢結果與運算均用 ISNULL 防止 NULL 傳播。
• 模組化：核心計算封裝於函數，預存程序負責流程控制與彙總，符合單一職責原則。
• 效能考量：函數為純量函數，大量資料呼叫時需注意效能；可考慮改為內嵌表值函數或直接在預存程序中以 JOIN 完成。