SQL Server 使用者定義函數 (UDF) 實戰模式 (1)：在 UPDATE 中封裝複雜計算邏輯

UPDATE 陳述式中**無法直接呼叫預存程序**，且常需依據多欄位/多表關聯進行複雜計算。**標量使用者定義函數 (Scalar UDF)** 成為封裝此類邏輯的最佳選擇：可在 SELECT/WHERE/SET 子句中像內建函數一樣呼叫，程式碼可重用、可維護、可測試。

場景：FMECA 風險分析關鍵度計算

業務邏輯：根據任務階段 (`TaskPhase`) 所屬任務 (`Task`) 的工時 (`TaskTime`) 與工作負擔百分比 (`WorkPercent`)，計算關鍵度貢獻值 (`Cvalue_M`)。

函數定義：`dbo.FUN_GETTIME`

CREATE FUNCTION [dbo].[FUN_GETTIME] (@TaskPhaseID INT)
RETURNS FLOAT
AS
BEGIN
    DECLARE @TaskID INT, @Hour FLOAT, @Percent FLOAT, @Result FLOAT;

    IF @TaskPhaseID IS NULL RETURN 0.0;

    -- 取得任務 ID 與工作負擔%
    SELECT @TaskID = TaskID, @Percent = ISNULL(WorkPercent, 0) / 100.0
    FROM TableTaskPhase
    WHERE ID = @TaskPhaseID;

    -- 取得任務工時
    SELECT @Hour = ISNULL(TaskTime, 0)
    FROM TableTask
    WHERE ID = @TaskID;

    SET @Result = @Hour * @Percent;
    RETURN @Result;
END;
GO

呼叫端預存程序：`dbo.PROC_CALCCA`

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
    JOIN TableRelation B ON B.ID = @ROID
    JOIN TableTaskPhase C ON C.ID = TableFMECA.TaskPhaseID
    WHERE TableFMECA.ROID = @ROID;

    SELECT @CA = SUM(ISNULL(Cvalue_M, 0))
    FROM TableFMECA
    WHERE ROID = @ROID;

    UPDATE TableRelation
    SET Criticality = @CA
    WHERE ID = @ROID;
END;
GO

關鍵優勢

1. 邏輯集中化：計算公式單一定義，多處呼叫 (SP、View、Computed Column、CHECK Constraint) 自動同步。
2. SQL 可組合性：UDF 可直接嵌入 `UPDATE ... SET col = dbo.Func(...) FROM ... JOIN ...`，避免游標逐列處理。
3. 參數化與空值處理：函數內部統一 `ISNULL`、`NULL` 判斷，呼叫端無需重複防禦性編碼。
4. 效能考量：標量 UDF 在大結果集逐列調用有函數呼叫開銷。SQL Server 2019+ **標量 UDF 內嵌 (Scalar UDF Inlining)** 可自動將簡單 UDF 轉為內聯運算式，大幅提升效能。舊版本可考慮改寫為 `CROSS APPLY` 或內聯 TVF。

最佳實務建議

• 命名慣例：`FUN_` 或 `fn_` 前綴 + 動詞 + 名詞 (如 `FUN_CalculateRiskFactor`)。
• 儘量使用 `SCHEMABINDING` 防止相依物件被意外修改/刪除：`CREATE FUNCTION ... WITH SCHEMABINDING AS ...`。
• 避免在 UDF 內執行資料修改 (DML)、動態 SQL、暫存表操作 (僅限多陳述式 TVF)。
• 大量資料集效能敏感場景：優先考慮 **內聯資料表值函數 (Inline TVF)** 或 `CROSS APPLY` 子查詢。