SQL Server 預存程序交易控制實戰範本：錯誤處理與提交/回滾模式

在預存程序中正確管理交易，是確保資料一致性的基石。本文提供一個生產級的交易控制範本，涵蓋：多步驟 DML 錯誤累積、@@ERROR 捕捉、條件式提交/回滾、標準化回傳碼，可直接套用於進貨、銷貨、庫存調整等業務流程。

核心範本程式碼

CREATE PROC usp_Stock
    @GoodsId      INT,
    @Number       INT,
    @StockPrice   MONEY,
    @SupplierId   INT,
    @EmpId        INT,
    @StockUnit    VARCHAR(50),
    @StockDate    DATETIME,
    @TotalMoney   MONEY,
    @ActMoney     MONEY,
    @BaseId       INT,
    @Description  NVARCHAR(255)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @error INT = 0;          -- 錯誤計數器

    BEGIN TRANSACTION;               -- 開啟交易

    /*------ 步驟 1：新增進貨主檔 ------*/
    INSERT INTO StockInfo
        (GoodsId, Number, StockPrice, SupplierId, EmpId,
         StockUnit, StockDate, TotalMoney, ActMoney, Description, BaseId)
    VALUES
        (@GoodsId, @Number, @StockPrice, @SupplierId, @EmpId,
         @StockUnit, @StockDate, @TotalMoney, @ActMoney, @Description, @BaseId);
    SET @error += @@ERROR;           -- 累加錯誤碼

    /*------ 步驟 2：維護庫存明細 (UPSERT 邏輯) ------*/
    IF EXISTS (SELECT 1 FROM dbo.InventoryInfo WHERE GoodId = @GoodsId)
    BEGIN
        -- 已有庫存記錄：累加數量與金額
        UPDATE dbo.InventoryInfo
        SET GNumber = GNumber + @Number,
            TotalMoney = TotalMoney + @TotalMoney
        WHERE GoodId = @GoodsId;
        SET @error += @@ERROR;
    END
    ELSE
    BEGIN
        -- 首次進貨：插入新庫存記錄
        DECLARE @GWarningNum INT;
        SELECT @GWarningNum = WarningNum FROM dbo.GoodsInfo WHERE GId = @GoodsId;

        INSERT INTO dbo.InventoryInfo
            (GoodId, GNumber, BaseId, GWarningNum, TotalMoney, Remark, CreateDate)
        VALUES
            (@GoodsId, @Number, @BaseId, @GWarningNum, @TotalMoney, N'第一次進貨', GETDATE());
        SET @error += @@ERROR;
    END

    /*------ 步驟 3：統一判斷提交或回滾 ------*/
    IF @error <> 0
    BEGIN
        ROLLBACK TRANSACTION;
        RETURN -1;   -- 失敗代碼
    END
    ELSE
    BEGIN
        COMMIT TRANSACTION;
        RETURN 1;    -- 成功代碼
    END
END;
GO

關鍵設計要點解析

1. 錯誤累積變數 @error

• 初始化為 0，每個 DML 陳述式後立即 SET @error += @@ERROR。
• @@ERROR 僅反映緊鄰前一條陳述式的錯誤狀態，必須即時捕捉，否則後續成功陳述式會將其重置為 0。
• 最終檢查 @error <> 0 即可知道交易期間是否發生任何錯誤。

2. 交易邊界明確

• BEGIN TRANSACTION 標示起點，COMMIT/ROLLBACK 標示終點。
• 所有業務邏輯 DML 必須包含在同一交易區塊，確保「全成或全不成」。

3. UPSERT 模式處理庫存

• 先檢查 EXISTS，存在則 UPDATE 累加，不存在則 INSERT 新記錄。
• 避免使用 MERGE（舊版本相容性）或分開的 SELECT/INSERT 造成競態條件。

4. 標準化回傳碼

• RETURN 1：交易成功提交。
• RETURN -1：交易回滾，呼叫端可依此判斷後續處理（如記錄日誌、拋出例外）。

5. 適用 SQL Server 2005+ 環境

若使用 SQL Server 2012+，建議改用 TRY...CATCH 結構配合 THROW，可捕捉嚴重度 11+ 的錯誤（@@ERROR 無法捕捉）。現代化寫法如下：

BEGIN TRY
    BEGIN TRANSACTION;
    -- 所有 DML 操作
    COMMIT TRANSACTION;
    RETURN 1;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    -- 記錄錯誤資訊：ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_LINE()
    RETURN -1;
END CATCH;

部署建議

1. 將此範本納入團隊開發規範，統一交易處理風格。
2. 針對高併發熱點表，評估鎖升級與死結風險，必要時加入 UPDLOCK、HOLDLOCK 提示。
3. 搭配應用層重試機制（指數退避），處理死結受害者被終結的情況。

採用結構化、可複用的交易控制模式，能顯著降低資料不一致風險，提升系統穩健度。