SQL Server 預存程序中事務處理三種寫法實戰範例

前言

預存程序中正確處理事務是保證資料一致性的關鍵。本文展示三種常見事務處理模式的完整程式碼範例，涵蓋 XACT_ABORT、@@ERROR 檢查、TRY...CATCH 結構化異常處理，助您選擇最適合的方案。

方式一：SET XACT_ABORT ON 自動回滾

IF EXISTS (SELECT * FROM dbo.sysobjects 
    WHERE id = OBJECT_ID(N'[dbo].[USP_ProcedureWithTransaction_Demo]') 
    AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE [dbo].[USP_ProcedureWithTransaction_Demo]
GO

CREATE PROCEDURE [dbo].[USP_ProcedureWithTransaction_Demo]
AS
BEGIN
    SET XACT_ABORT ON  -- 發生執行錯誤自動回滾整個事務
    BEGIN TRANSACTION
    
    INSERT INTO Lock(LockTypeID) VALUES('A')  -- 故意錯誤：LockTypeID 為 int
    UPDATE Lock SET LockTypeID = 2 WHERE LockID = 32
    
    COMMIT TRANSACTION
    SET XACT_ABORT OFF
END
GO

特點：簡潔，運行時錯誤（如類型轉換失敗、主鍵衝突）自動觸發全事務回滾，無需手動判斷。

方式二：@@ERROR 手動檢查回滾

IF EXISTS (SELECT * FROM dbo.sysobjects 
    WHERE id = OBJECT_ID(N'[dbo].[USP_ProcedureWithTransaction_Demo]') 
    AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE [dbo].[USP_ProcedureWithTransaction_Demo]
GO

CREATE PROCEDURE [dbo].[USP_ProcedureWithTransaction_Demo]
AS
BEGIN
    BEGIN TRANSACTION
    
    INSERT INTO Lock(LockTypeID) VALUES('A')  -- 故意錯誤
    UPDATE Lock SET LockTypeID = 1 WHERE LockID = 32
    
    COMMIT TRANSACTION
    
    IF (@@ERROR <> 0)  -- 僅檢查最後一條語句錯誤
        ROLLBACK TRANSACTION
END
GO

缺點：@@ERROR 僅反映最後一條語句狀態，若中間語句失敗但後續成功，無法捕捉；需在每條關鍵語句後檢查並配合 GOTO 跳轉，代碼冗長易出錯。

方式三：TRY...CATCH 結構化異常處理（推薦）

IF EXISTS (SELECT * FROM dbo.sysobjects 
    WHERE id = OBJECT_ID(N'[dbo].[USP_ProcedureWithTransaction_Demo]') 
    AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE [dbo].[USP_ProcedureWithTransaction_Demo]
GO

CREATE PROCEDURE [dbo].[USP_ProcedureWithTransaction_Demo]
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION
        
        UPDATE Lock SET LockTypeID = 1 WHERE LockID = 32
        INSERT INTO Lock(LockTypeID) VALUES('A')  -- 故意錯誤
        
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION
        -- 可在此記錄錯誤資訊：ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_LINE() 等
    END CATCH
END
GO

-- 測試執行
EXEC [USP_ProcedureWithTransaction_Demo]

優勢：

• 結構清晰，業務邏輯與錯誤處理分離
• 捕捉批次中所有運行時錯誤（編譯錯誤除外）
• 可獲取詳細錯誤資訊便於診斷
• SQL Server 2005+ 版本首選方案

最佳實踐建議

1. 優先使用 TRY...CATCH，代碼可維護性最高
2. 事務範圍盡量縮小，減少鎖持有時間
3. 避免在事務中進行網路呼叫、使用者互動等耗時操作
4. 嵌套事務僅計數器遞增，真正提交/回滾以最外層為準