SQL Server 預存程序 IF ELSE 條件邏輯實戰：處理外鍵允許 NULL 的插入情境

情境：資料表 A 主鍵為 IDENTITY，資料表 B 參考 A 的主鍵為外鍵且允許 NULL。應用程式以 int 類型接收參數，無法傳遞 NULL（未指定時預設 0）。解決方案：在預存程序內以 IF @SourceID = 0 判斷，決定是否在 INSERT 陳述式中包含 SourceID 欄位。

完整預存程序範例

CREATE PROCEDURE [dbo].[P_Form_Control_Info_Add]
    @TypeName varchar(20),
    @Description varchar(50),
    @CtlColSpan int,
    @Sort int,
    @SourceID int,      -- 0 代表不指定，對應 NULL
    @FieldID int,
    @TableID int
AS
BEGIN
    SET NOCOUNT ON;

    IF @SourceID = 0
    BEGIN
        -- 不指定 SourceID，讓資料庫預設值（NULL）生效
        INSERT INTO T_Form_Control_Info (
            [TypeName], [Description], [CtlColSpan], [Sort],
            [FieldID], [TableID]
        ) VALUES (
            @TypeName, @Description, @CtlColSpan, @Sort,
            @FieldID, @TableID
        );
    END
    ELSE
    BEGIN
        -- 指定 SourceID
        INSERT INTO T_Form_Control_Info (
            [TypeName], [Description], [CtlColSpan], [Sort],
            [SourceID], [FieldID], [TableID]
        ) VALUES (
            @TypeName, @Description, @CtlColSpan, @Sort,
            @SourceID, @FieldID, @TableID
        );
    END

    -- 回傳新增列的識別值
    RETURN SCOPE_IDENTITY();
END
GO

關鍵技巧解析

1. 語意化 0 為 NULL：應用層約定 0 代表「未提供」，預存程序內轉換為不帶入欄位，利用資料表預設值（DEFAULT NULL）寫入 NULL。
2. 欄位清單明確列出：兩個 INSERT 陳述式欄位清單不同，避免 INSERT INTO table VALUES (..., NULL, ...) 寫法（雖然可行但較不明確）。
3. SCOPE_IDENTITY()：回傳當前作用域內產生的識別值，比 @@IDENTITY 安全（不受觸發器影響）。
4. SET NOCOUNT ON：抑制「受影響的行數」訊息，減少網路往返。

替代寫法：動態 SQL 單一 INSERT

DECLARE @cols NVARCHAR(MAX) = N'[TypeName],[Description],[CtlColSpan],[Sort],[FieldID],[TableID]';
DECLARE @vals NVARCHAR(MAX) = N'@TypeName,@Description,@CtlColSpan,@Sort,@FieldID,@TableID';

IF @SourceID <> 0
BEGIN
    SET @cols += N',[SourceID]';
    SET @vals += N',@SourceID';
END

DECLARE @sql NVARCHAR(MAX) = N'INSERT INTO T_Form_Control_Info (' + @cols + N') VALUES (' + @vals + N');';
EXEC sp_executesql @sql,
    N'@TypeName varchar(20),@Description varchar(50),@CtlColSpan int,@Sort int,@SourceID int,@FieldID int,@TableID int',
    @TypeName,@Description,@CtlColSpan,@Sort,@SourceID,@FieldID,@TableID;

動態 SQL 版本較靈活，但需注意 SQL 注入防護（此處使用參數化 sp_executesql 安全）。

擴充建議

• 若多個可選外鍵欄位，可改用 CASE 運算式或 IIF (SQL 2012+) 建構欄位/值清單。
• 搭配 TRY...CATCH 處理外鍵違反、唯一鍵衝突等錯誤，回傳錯誤碼給呼叫端。
• 大量批次新增時，考慮使用 Table-Valued Parameter (TVP) 傳遞資料集，避免逐筆呼叫預存程序。

善用 IF ELSE 控制 INSERT 欄位清單，能優雅解決應用層無法傳遞 NULL 與資料庫允許 NULL 的不匹配問題。