SQL Server 存储过程开发三层结构规范

以下是《SQL Server 存储过程开发三层结构规范》的正式文档结构，适用于企业级数据库应用开发场景，有助于团队协作、代码审查与自动化运维：

📘 SQL Server 存储过程开发三层结构规范

一、架构设计总览

三层结构简介

层级	命名约定	主要职责
主业务层（Main）	`sp_<模块名>_<功能名>_main`	控制业务主流程，负责对外接口
事务控制层（Tx）	`sp_<模块名>_<功能名>_tx`	管理事务边界与子功能协调
基础功能层（Fn）	`sp_<模块名>_<功能名>_fn_<功能名>`	实现原子功能逻辑，便于复用

二、命名规范

模块名、功能名采用小写英文下划线分隔，表达清晰业务含义。
所有存储过程前缀统一使用 sp_。
示例：
- 主业务层：sp_order_create_main
- 事务控制层：sp_order_create_tx
- 基础功能层：sp_order_create_fn_inventory

三、各层职责定义

🔷 1. 主业务层（Main）

内容	要求
输入参数	仅接收应用调用层传入的业务参数
调用形式	只调用对应的 `_tx` 层过程
日志	可打印起始日志，但业务日志由 Tx 层统一处理
返回形式	`SELECT 200 AS code, '成功' AS msg`，可附带业务ID等字段
约束	不直接处理事务，不访问数据库表，不直接嵌入业务逻辑

✅ 面向应用系统的唯一调用入口。

🔷 2. 事务控制层（Tx）

内容	要求
输入参数	继承主层参数，并补充必要中间参数
输出参数	必须提供 `@ResultCode` 和 `@ResultMsg`
事务控制	必须包含 `BEGIN TRAN / COMMIT / ROLLBACK`
异常处理	使用 `TRY...CATCH` 捕获所有异常并回滚事务
日志	统一调用 `sp_log_error`、`sp_log_trace` 等日志过程
调用	只调用 `fn_` 前缀的功能层过程，保证原子性

✅ 是事务安全与一致性的守护者。

🔷 3. 基础功能层（Fn）

内容	要求
功能粒度	单一职责，易于复用和测试
输入/输出	使用参数或表变量作为输入输出
不包含	事务控制、异常捕获、日志打印、SELECT 输出
命名	`sp_<模块名>_<功能名>_fn_<功能子项>`，如 `sp_order_create_fn_price`

✅ 聚焦功能实现，保持高内聚低耦合。

四、错误处理与返回标准

错误输出结构

SELECT 500 AS code, ERROR_MESSAGE() AS msg;

成功输出结构

SELECT 200 AS code, '处理成功' AS msg, @OrderId AS order_id;

日志规范

错误日志过程：sp_log_error(@ProcName, @ErrMsg)
操作日志过程：sp_log_trace(@ProcName, @Detail)

五、标准开发模板

✅ 主业务层模板（Main）

CREATE PROCEDURE sp_user_register_main@UserName NVARCHAR(50),@Password NVARCHAR(50)
AS
BEGINDECLARE @ResultCode INT, @ResultMsg NVARCHAR(200);EXEC sp_user_register_tx@UserName = @UserName,@Password = @Password,@ResultCode = @ResultCode OUTPUT,@ResultMsg = @ResultMsg OUTPUT;SELECT @ResultCode AS code, @ResultMsg AS msg;
END

✅ 事务控制层模板（Tx）

CREATE PROCEDURE sp_user_register_tx@UserName NVARCHAR(50),@Password NVARCHAR(50),@ResultCode INT OUTPUT,@ResultMsg NVARCHAR(200) OUTPUT
AS
BEGINBEGIN TRYBEGIN TRAN;EXEC sp_user_register_fn_check @UserName;EXEC sp_user_register_fn_insert @UserName, @Password;COMMIT TRAN;SET @ResultCode = 200;SET @ResultMsg = '用户注册成功';END TRYBEGIN CATCHROLLBACK TRAN;SET @ResultCode = 500;SET @ResultMsg = ERROR_MESSAGE();EXEC sp_log_error 'sp_user_register_tx', @ResultMsg;END CATCH
END

✅ 基础功能层模板（Fn）

CREATE PROCEDURE sp_user_register_fn_insert@UserName NVARCHAR(50),@Password NVARCHAR(50)
AS
BEGININSERT INTO Users(UserName, PasswordHash)VALUES (@UserName, HASHBYTES('SHA2_256', @Password));
END

六、附加建议

方面	建议
模块组织	按模块分类存储过程脚本文件夹
CI/CD	建议使用脚本版本控制系统，如 Flyway、DbUp
安全性	权限隔离，开发只读，发布执行权限
单元测试	为每个 `fn_` 层编写测试用例，确保幂等性
参数命名	使用前缀 `@in_`, `@out_` 可增强可读性（可选）