当前位置：首页 > backend >正文

SQL Server 存储过程开发规范

backend 2025/5/6 10:16:58

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集成