SQL Server 存儲過程開發規范（高級版）
1. 總則
1.1 目標
本規范旨在：
-
提高存儲過程的事務一致性、異常可追蹤性、錯誤透明度。
-
統一日志記錄、錯誤碼管理、鏈路追蹤（Trace ID）。
-
支持復雜事務場景（嵌套事務、分布式事務）。
-
為持續集成（CI/CD）提供自動化測試與發布支持。
2. 存儲過程分層與職責
| 層次 | 控制事務 | 記錄異常日志 | 返回狀態碼 | Trace ID | 說明 |
|---|---|---|---|---|---|
| Main（主流程） | ? | ? | ? | ? | 生成并傳遞 Trace ID，匯總狀態 |
| SubTx（子事務） | ? | ? | ? | ? | 獨立事務單元，異常即回滾 |
| Func（功能操作） | ? | 可選 | 可選 | ? | 查詢或計算操作 |
3. 日志表設計（Log表標準化）
設計統一、專業的日志表，以支持問題追蹤和運維排查。
3.1 錯誤日志表結構（示例）
CREATE TABLE dbo.Proc_Error_Log (LogId BIGINT IDENTITY(1,1) PRIMARY KEY,TraceId UNIQUEIDENTIFIER NOT NULL, -- 鏈路IDProcName NVARCHAR(200) NOT NULL, -- 存儲過程名稱ErrorCode INT NOT NULL, -- 錯誤碼ErrorMessage NVARCHAR(2000) NOT NULL, -- 錯誤信息StackTrace NVARCHAR(MAX) NULL, -- 堆棧追蹤信息（可選）CreateTime DATETIME2 NOT NULL DEFAULT GETUTCDATE(), -- UTC時間InputParams NVARCHAR(MAX) NULL, -- 輸入參數快照（JSON格式）ExtraInfo NVARCHAR(MAX) NULL -- 額外上下文信息（如服務器名、環境）
);
3.2 日志寫入示例
INSERT INTO dbo.Proc_Error_Log (TraceId, ProcName, ErrorCode, ErrorMessage, InputParams)
VALUES (@TraceId, 'proc_Order_Shipment_Main', @code, ERROR_MESSAGE(), @InputParams);
4. 統一錯誤代碼管理策略
4.1 錯誤碼分層設計
| 范圍 | 說明 |
|---|---|
| 0 | 成功 |
| 1000-1999 | 業務校驗失敗（如庫存不足、余額不足） |
| 2000-2999 | 數據庫操作失敗（如更新失敗） |
| 3000-3999 | 外部系統調用失敗（如支付系統超時） |
| 4000-4999 | 系統異常（如死鎖、不可預知異常） |
所有錯誤碼及含義應維護一張獨立表或配置文件，便于統一維護和前端適配。
5. Trace ID設計與使用
5.1 生成與傳遞
-
在主流程入口生成 Trace ID（UUID）。
-
通過參數傳遞到每個子事務和日志記錄，保證鏈路統一。
DECLARE @TraceId UNIQUEIDENTIFIER = NEWID();
EXEC proc_Order_Shipment_Main @OrderId, @TraceId, @code OUTPUT, @msg OUTPUT;
5.2 傳遞規范
-
所有子過程均必須攜帶
@TraceId參數。 -
日志記錄必須關聯
TraceId，便于串聯問題定位。
6. 復雜事務控制規范
6.1 嵌套子事務（Savepoint）
子事務內部采用 SAVE TRANSACTION，保證局部回滾而非破壞主事務。
BEGIN TRANSACTION;
SAVE TRANSACTION SavePoint_SubTx1;BEGIN TRY-- 子操作
END TRY
BEGIN CATCHROLLBACK TRANSACTION SavePoint_SubTx1;THROW;
END CATCH
6.2 分布式事務（如調用外部數據庫）
必須顯式使用 BEGIN DISTRIBUTED TRANSACTION，并加快超時時間控制。
7. 存儲過程單元測試體系
7.1 Mock數據表設計
為存儲過程測試，設計專門的測試版表（如 Order_Test、Inventory_Test），與正式表結構一致，但獨立存在。
-
測試存儲過程前，插入測試數據。
-
測試結束后，清理數據，保證測試冪等性。
7.2 自動化測試腳本示例
-- Arrange
INSERT INTO Order_Test(OrderId, Status) VALUES (1, 'Pending');-- Act
DECLARE @code INT, @msg NVARCHAR(500);
EXEC proc_Order_Shipment_Main @OrderId = 1, @TraceId = NEWID(), @code OUTPUT, @msg OUTPUT;-- Assert
IF @code != 0PRINT 'Test Failed: ' + @msg;
ELSEPRINT 'Test Passed';
可以將測試腳本集成到CI流程中，保證每次變更均經過驗證。
8. 自動化部署策略
8.1 腳本編寫規范
-
存儲過程腳本必須支持冪等執行（存在則更新）。
-
示例：
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[proc_Order_Shipment_Main]') AND type = N'P')DROP PROCEDURE dbo.proc_Order_Shipment_Main;
GO
CREATE PROCEDURE dbo.proc_Order_Shipment_Main
AS
BEGIN-- 定義
END
GO
8.2 自動發布流程
-
開發者提交存儲過程腳本至版本庫（如 Git）。
-
CI Pipeline自動檢測是否有變更。
-
自動化執行發布腳本，部署到測試環境或正式環境。
-
發布前自動備份舊版本存儲過程。
最后總結
| 關鍵能力 | 規范提升點 |
|---|---|
| 日志記錄 | 統一結構化存儲，關聯Trace ID |
| 錯誤處理 | 標準化錯誤碼體系 |
| 事務控制 | 支持局部回滾、分布式事務 |
| 調試追蹤 | Trace ID貫穿調用鏈 |
| 測試保障 | mock表+自動化測試 |
| 自動部署 | 冪等腳本+CI集成 |
通過這些規范，能夠讓SQL Server存儲過程開發真正具備企業級生產力，同時大幅降低維護成本和故障排查復雜度。
