SQL Server 預存程序交易處理三大模式完整解析：XACT_ABORT、@@ERROR 判斷、TRY/CATCH 異常處理

預存程序中正確處理交易是資料一致性的關鍵。預設下 SQL Server 遇到執行時錯誤 (如違反 NOT NULL) 僅回滾**該陳述式**，繼續執行後續陳述式，導致交易部分成功。本文對比三種交易控制模式並給出標準範本。

問題重現：預設行為的陷阱

CREATE TABLE demo (id INT NOT NULL);
GO
BEGIN TRAN
    INSERT INTO demo VALUES (NULL);  -- 違反 NOT NULL，報錯
    INSERT INTO demo VALUES (2);     -- 仍會執行成功！
COMMIT TRAN;
GO
SELECT * FROM demo;  -- 發現值 2 被寫入，交易未整體回滾

模式一：SET XACT_ABORT ON (最簡潔、推薦一般場景)

CREATE PROC usp_Demo_XactAbort AS
BEGIN
    SET XACT_ABORT ON;  -- 遇任何執行時錯誤：終止批次、回滾整個交易
    BEGIN TRAN
        UPDATE ...;
        INSERT ...;
        DELETE ...;
    COMMIT TRAN;
END
GO

• 優點：零程式碼侵入、涵蓋所有執行時錯誤 (含條件約束、型別轉換失敗等)。
• 缺點：無法針對特定錯誤做差異化處理 (如記錄日誌、重試)。
• 注意：不影響編譯時錯誤 (語法錯誤)、不影響 `RAISERROR`/`THROW` 主動拋出的錯誤 (需搭配 `TRY/CATCH`)。

模式二：@@ERROR 逐句檢查 (SQL 2000 相容、精細控制)

CREATE PROC usp_Demo_ErrorCheck AS
BEGIN
    BEGIN TRAN
        UPDATE ...;
        IF @@ERROR <> 0 BEGIN ROLLBACK TRAN; RETURN -1; END

        DELETE ...;
        IF @@ERROR <> 0 BEGIN ROLLBACK TRAN; RETURN -1; END

    COMMIT TRAN;
    RETURN 0;
END
GO

• 優點：可針對每句自訂錯誤處理、回傳自訂錯誤碼。
• 缺點：程式碼冗長、易遺漏檢查、@@ERROR 重置快 (必須緊接陳述式後檢查)、不捕捉嚴重度 20+ 系統錯誤。

模式三：TRY/CATCH 結構化異常處理 (SQL 2005+、現代標準)

CREATE PROC usp_Demo_TryCatch AS
BEGIN
    SET XACT_ABORT ON;  -- 建議搭配：確保嚴重錯誤也能進 CATCH
    BEGIN TRY
        BEGIN TRAN
            UPDATE ...;
            DELETE ...;
        COMMIT TRAN;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRAN;
        -- 記錄錯誤資訊
        INSERT INTO ErrorLog (ProcName, ErrorNumber, ErrorMessage, ErrorTime)
        VALUES (OBJECT_NAME(@@PROCID), ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE());
        -- 可選：重新拋出給上層
        THROW;
        RETURN -1;
    END CATCH
    RETURN 0;
END
GO

關鍵系統函數 (CATCH 區塊專用)

三大模式選擇決策表

完整生產級範本 (TRY/CATCH + XACT_ABORT + 錯誤日誌)

CREATE PROC usp_BusinessOperation @Param1 INT, @Param2 NVARCHAR(50) AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;  -- 兜底：任何執行時錯誤直接回滾

    BEGIN TRY
        BEGIN TRAN
            -- 核心業務邏輯
            UPDATE TableA SET Col1 = @Param2 WHERE Id = @Param1;
            INSERT INTO TableB (ColA, ColB) VALUES (@Param1, @Param2);
            DELETE FROM TableC WHERE RefId = @Param1;
        COMMIT TRAN;
        RETURN 0;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRAN;
        
        -- 記錄詳細錯誤
        DECLARE @ErrMsg NVARCHAR(4000) = ERROR_MESSAGE();
        INSERT INTO dbo.ErrorLog (ProcName, ErrorNumber, ErrorSeverity, ErrorState, 
                                  ErrorProcedure, ErrorLine, ErrorMessage, LogTime)
        VALUES (OBJECT_NAME(@@PROCID), ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(),
                ERROR_PROCEDURE(), ERROR_LINE(), @ErrMsg, SYSDATETIME());
        
        -- 回傳給呼叫端 (可選)
        RAISERROR('Business operation failed: %s', 16, 1, @ErrMsg);
        RETURN -1;
    END CATCH
END
GO

核心觀念釐清

• @@TRANCOUNT：目前交易巢狀層級，`BEGIN TRAN` +1，`COMMIT` -1，`ROLLBACK` 歸零。
• XACT_ABORT 僅影響執行時錯誤 (Run-time errors)；編譯錯誤、中斷連線、嚴重度 20+ 錯誤不受控制。
• TRY/CATCH 不捕捉編譯錯誤、連線中斷、嚴重度 10- 的資訊性訊息。
• THROW (SQL 2012+) 保留原始錯誤堆疊，優於 `RAISERROR`。

採用「TRY/CATCH + SET XACT_ABORT ON + 統一錯誤日誌」作為團隊預存程序交易標準，可兼顧正確性、可觀測性與維護效率。