SQL Server 預存程序與交易控制實戰範例

前言

預存程序封裝業務邏輯，交易確保資料一致性（ACID）。兩者結合是資料庫程式設計的核心技能。本文透過完整範例，示範預存程式結構、交易啟動/提交/回滾、錯誤處理（@@ERROR/TRY...CATCH）、儲存點、巢狀交易計數（@@TRANCOUNT），以及經典的「轉帳」業務邏輯實作。

一、預存程序基礎結構

CREATE PROCEDURE dbo.ProcName
    @Param1 DataType,
    @Param2 DataType OUTPUT
AS
BEGIN
    SET NOCOUNT ON;          -- 關閉「受影響的行數」訊息，減少網路傳輸
    -- 變數宣告
    DECLARE @Var1 DataType;
    -- 主要邏輯
    ...
    RETURN 0;                -- 0 表成功，非 0 表自訂錯誤碼
END;

二、交易控制核心指令

三、經典範例 1：基本交易範本 (SQL 2000 風格)

CREATE PROCEDURE dbo.TestTran
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION Tran_Test;
    SAVE TRANSACTION Tran_Test;  -- 設定儲存點

    INSERT INTO Table1 (Content) VALUES ('43332');

    IF @@ERROR <> 0
    BEGIN
        RAISERROR('Insert data error!', 16, 1);
        ROLLBACK TRANSACTION Tran_Test;
        RETURN -1;
    END

    IF @@TRANCOUNT > 0
        COMMIT TRANSACTION Tran_Test;
    RETURN 0;
END;

四、經典範例 2：銀行轉帳業務 (完整錯誤處理)

CREATE PROCEDURE dbo.Proc_TakeMoney
    @CardID1 VARCHAR(20),   -- 匯款帳號
    @Pwd     INT,           -- 匯款密碼
    @Money   MONEY,         -- 匯款金額
    @CardID2 VARCHAR(20)    -- 收款帳號
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Err INT = 0;

    -- 驗證匯款帳號密碼
    IF EXISTS (SELECT 1 FROM CardInfo WHERE CardID = @CardID1 AND Pass = @Pwd)
    BEGIN
        BEGIN TRANSACTION;

        -- 收款帳號加款
        UPDATE CardInfo SET Balance = Balance + @Money WHERE CardID = @CardID2;
        SET @Err = @Err + @@ERROR;

        -- 匯款帳號扣款
        UPDATE CardInfo SET Balance = Balance - @Money WHERE CardID = @CardID1;
        SET @Err = @Err + @@ERROR;

        -- 記錄交易流水
        INSERT INTO TransInfo (TransDate, CardID, TransType, TransMoney)
        VALUES (DEFAULT, @CardID1, '支取', @Money);
        SET @Err = @Err + @@ERROR;

        INSERT INTO TransInfo (TransDate, CardID, TransType, TransMoney)
        VALUES (DEFAULT, @CardID2, '存入', @Money);
        SET @Err = @Err + @@ERROR;

        -- 回傳最新兩筆流水供前端確認
        SELECT TOP 2 * FROM TransInfo ORDER BY TransDate DESC;

        -- 統一判斷錯誤
        IF @Err <> 0
            ROLLBACK TRANSACTION;
        ELSE
            COMMIT TRANSACTION;
    END
    ELSE
    BEGIN
        PRINT '密碼錯誤';
        RETURN -1;
    END
END;

五、現代化錯誤處理：TRY...CATCH (SQL 2005+)

SQL Server 2005 引入結構化例外處理，程式碼更清晰、可維護：

CREATE PROCEDURE dbo.ModernTransfer
    @FromAccount VARCHAR(20), @ToAccount VARCHAR(20), @Amount MONEY
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;
            UPDATE Accounts SET Balance -= @Amount WHERE AccountID = @FromAccount;
            UPDATE Accounts SET Balance += @Amount WHERE AccountID = @ToAccount;
            INSERT INTO TxnLog (FromAcct, ToAcct, Amount, TxnTime)
            VALUES (@FromAccount, @ToAccount, @Amount, SYSDATETIME());
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        -- 記錄錯誤細節
        DECLARE @Msg NVARCHAR(4000) = ERROR_MESSAGE();
        DECLARE @Sev INT = ERROR_SEVERITY();
        DECLARE @St  INT = ERROR_STATE();
        RAISERROR(@Msg, @Sev, @St);  -- 重新拋出給呼叫端
        RETURN -1;
    END CATCH
    RETURN 0;
END;

六、關鍵觀念釐清

1. 交易 ≠ 預存程序：交易可在預存程序外使用（如應用程式中 SqlTransaction），預存程序也可不含交易。
2. 巢狀交易實為計數器：BEGIN TRAN 遞增 @@TRANCOUNT，COMMIT 遞減，只有歸零時真正寫入磁碟。ROLLBACK 直接歸零並撤銷所有。
3. 儲存點：允許在大交易中部分回滾，但不支援部分提交。
4. 隱含交易：DDL（CREATE/ALTER/DROP）會自動提交前一筆交易，謹慎混用。

七、呼叫端範例

DECLARE @From VARCHAR(20) = '1010357612345678',
        @To   VARCHAR(20) = '1010357612121134',
        @Pwd  INT = 888888,
        @Amt  MONEY = 20;

EXEC dbo.Proc_TakeMoney @From, @Pwd, @Amt, @To;

八、總結

預存程序 + 交易控制 = 資料一致性與業務邏輯封裝的雙重保障。掌握 BEGIN/COMMIT/ROLLBACK、@@TRANCOUNT、SAVE TRANSACTION、TRY...CATCH 等機制，並遵循「驗證先行、錯誤即時回滾、成功才提交」原則，即能撰寫健壯的資料庫業務程式。