SQL Server 存儲過程開發規範與最佳實踐指南

存儲過程是 SQL Server 邏輯封裝、性能優化、安全控制的核心載體。但濫用、寫法不規範會導致維護噩夢。本文從命名、參數、錯誤處理、事務、性能、安全、版本管理七大維度，制定企業級開發規範，附標準模板與代碼審查清單。

1. 命名規範

2. 標準模板 (含文檔、參數、錯誤、事務、日志)

CREATE PROCEDURE usp_Module_Action_Object
    @InputParam1 INT,
    @InputParam2 NVARCHAR(100),
    @OutputParam INT OUTPUT,
    @ReturnCode INT = 0 OUTPUT  -- 統一返回碼：0=成功，>0 業務錯誤碼，<0 系統錯誤碼
AS
BEGIN
    SET NOCOUNT ON;           -- 禁止返回受影響行數，減少網絡開銷
    SET XACT_ABORT ON;        -- 運行時錯誤自動回滾事務 (配合 TRY/CATCH)
    
    -- 參數驗證
    IF @InputParam1 <= 0
    BEGIN
        RAISERROR('Invalid InputParam1', 16, 1);
        SET @ReturnCode = 1001; RETURN;
    END
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- 核心業務邏輯
        -- ...
        
        COMMIT TRANSACTION;
        SET @ReturnCode = 0;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        
        -- 錯誤日志寫入 (統一錯誤表)
        EXEC usp_Log_Error 
            @ProcName = OBJECT_NAME(@@PROCID),
            @ErrorNumber = ERROR_NUMBER(),
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE(),
            @ErrorLine = ERROR_LINE();
        
        SET @ReturnCode = -ERROR_NUMBER();  -- 負數表示系統錯誤
    END CATCH
END;
GO

3. 參數設計原則

• 輸入參數默認值：僅對真正可選參數設置 = DEFAULT，避免隱式轉換錯誤。
• 大對象參數：VARCHAR(MAX)/NVARCHAR(MAX)/VARBINARY(MAX) 替代舊 TEXT/NTEXT/IMAGE。
• 表值參數 (TVP)：批量傳遞集合數據，替代 XML/字符串分裂/臨時表，2008+ 必須掌握。
  CREATE TYPE dbo.OrderDetailType AS TABLE (ProductId INT, Qty INT);
• 避免 OUTPUT 濫用：僅真正需要返回值時使用，多結果集改用 SELECT 返回。

4. 錯誤處理與事務最佳實踐

• TRY/CATCH + XACT_ABORT ON：捕獲運行時錯誤 (死鎖、約束違反、轉換失敗)，編譯錯誤 (語法、對象不存在) 無法捕獲**，需部署前靜態檢查。
• XACT_STATE() 判斷事務狀態：1=可提交, -1=不可提交(必須回滾), 0=無事務。
• 嵌套事務陷阱：SQL Server 不支持真正嵌套事務，@@TRANCOUNT 計數器模式。子過程若開啟事務，外層回滾會回滾所有。建議：僅最外層業務邊界控制事務，內層過程不開啟事務，或檢查 @@TRANCOUNT = 0 才開啟。
• 死鎖重試邏輯：應用層實現 (指數退避)，存儲過程內不建議重試 (持有鎖時間不可控)。