您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 預存程序實戰：交易控制、錯誤回滾與資料遷移範本

編輯：關於SqlServer

以「成績遷移」為例的完整預存程式架構

實務上常需在預存程序中執行跨表資料遷移，並保證原子性（全成或全滾）。本文展示標準 TRY...CATCH + 顯式交易模板，並解析關鍵細節。

完整程式碼

USE [new_wzjw];
GO
SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON;
GO

CREATE PROC [dbo].[成績管理_過程化成績錄入_資料遷移]
    @kcId INT  -- 課程 ID 參數
AS
BEGIN
    SET NOCOUNT ON;  -- 減少網路往返 (DONE_IN_PROC 訊息)

    BEGIN TRY
        BEGIN TRAN;  -- 開啟顯式交易

        -- 核心遷移邏輯：只遷移尚未存在的學生選課記錄
        INSERT INTO [web_inputCj_xsxkb]
            ([xh], [xm], [xxxzdm], [xxxzmc], [cjxzdm], [kcid], [lrsj], [manuAdd])
        SELECT
            xh, xm, xxxzdm, xxxzmc, cjxzdm, kcid, lrsj, manualAdd
        FROM [web_inputCj_cjb] AS a
        WHERE a.kcId = @kcId
          AND NOT EXISTS (
              SELECT 1 FROM [web_inputCj_xsxkb] AS b
              WHERE a.xh = b.xh AND a.kcId = b.kcid
          );

        COMMIT TRAN;  -- 成功提交
        RETURN 0;     -- 0 = 成功
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRAN;  -- 任何錯誤全盤回滾
        -- 可選：記錄錯誤至 Log 表
        -- INSERT INTO ErrorLog (...) VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), ...);
        RETURN 1;     -- 1 = 失敗
    END CATCH
END;
GO

關鍵設計點解析

項目	說明
`SET NOCOUNT ON`	抑制「(n 行受影響)」訊息，減少客戶端網路負載與 ADO.NET 解析開銷。
`BEGIN TRAN` 位置	放在 `TRY` 區塊內，確保語法錯誤（編譯期）不會開啟交易。
`NOT EXISTS` vs `LEFT JOIN ... WHERE b.id IS NULL`	`NOT EXISTS` 語意清晰、對 NULL 安全、優化器通常產生 Left Anti Semi Join，效能佳。
`@@TRANCOUNT > 0` 判斷	防止巢狀呼叫時重複 Rollback 導致錯誤。
回傳值 `0/1`	慣例：0=成功、非 0=失敗；呼叫端可用 `SqlParameter Direction=ReturnValue` 接收。
錯誤資訊函數	`ERROR_NUMBER()`, `ERROR_MESSAGE()`, `ERROR_LINE()`, `ERROR_PROCEDURE()` 僅在 `CATCH` 區塊內有效。

進階擴充：錯誤日誌記錄

CREATE TABLE dbo.ProcErrorLog (
    LogID INT IDENTITY PRIMARY KEY,
    ProcName SYSNAME,
    ErrorNum INT,
    ErrorMsg NVARCHAR(4000),
    ErrorLine INT,
    LogTime DATETIME2 DEFAULT SYSDATETIME()
);

-- 在 CATCH 區塊加入
INSERT INTO dbo.ProcErrorLog (ProcName, ErrorNum, ErrorMsg, ErrorLine)
VALUES (ERROR_PROCEDURE(), ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_LINE());

呼叫端 (C#) 範例

using (var conn = new SqlConnection(connStr))
using (var cmd = new SqlCommand("dbo.成績管理_過程化成績錄入_資料遷移", conn)) {
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@kcId", 123);
    var ret = cmd.Parameters.Add("@Return", SqlDbType.Int);
    ret.Direction = ParameterDirection.ReturnValue;
    
    conn.Open();
    cmd.ExecuteNonQuery();
    int result = (int)ret.Value;  // 0 成功、1 失敗
}