开头进公司第二天Leader 扔给我一个六百多行的历史存储过程让我改一个单据状态字段。我打开一看没有头部注释、没有分段、命名全是ab游标套游标事务开了三个没一个回滚逻辑对齐。我花了一整天加一个通宵才理清它的执行顺序最后只改了两行。当时我就想这种代码如果多来几个数据库迟早变成事故现场。后来我负责牵头整理公司内部的 SQL Server 存储过程开发规范从命名、结构、错误处理、事务控制、性能红线到安全审计事无巨细地落成了一份团队模板。这篇文章就是这份规范背后的完整拆解思路。适合刚接触 SQL Server 不久的后端开发、DBA 新人以及那些被历史代码折磨过、想在公司内部推动规范落地但又怕被喷“管太宽”的团队负责人。我会尽量把“为什么这么定”讲透而不只是给一堆条条框框。1. 写规范之前先想清楚这三件事1.1 为什么公司模板普遍被无视很多公司不是没有规范而是规范文档躺在 Wiki 里吃灰。我见过好几份所谓“内部模板”通篇都是抽象理念——什么“命名要有意义”“代码要清晰易读”“注意性能”。这些话说得都对但没人做。因为大家看完之后不知道具体怎么算“有意义”、多清晰才算“易读”性能红线到底卡在哪一行。更现实的问题是很多规范只写了“你要怎么做”没写“你为什么要这么做”。开发者在新写一个存储过程的时候脑子里考虑的是功能先跑通至于参数要不要加前缀、错误要不要统一捕获这些事在没人强制、没人检查的情况下默认是能省则省。等到线上出了死锁、出了隐式转换导致索引失效、出了权限绕过问题才回头翻规范发现规范根本没覆盖这些场景。所以我在整理这套规范时第一个原则就是强制项必须少而硬不解释的规范活不过第一次代码评审。1.2 一套能落地的规范应该长什么样我理想的规范不是一本几十页的手册而是三部分一份一页纸的强制清单、一套可直接套用的模板脚本、一份常见坑的 review 检查表。强制清单负责拦住底线问题比如没写 SET NOCOUNT、事务不回滚、动态 SQL 拼字符串这种会被直接打回的缺陷模板脚本解决的是“从零开始怎么写”的问题一个文件头加一个错误处理骨架复制进去改业务就行检查表是给评审人用的不是给作者用的评审的时候逐条勾不靠感觉打分。另一个落地关键是工具绑定。我们团队当时统一装了 SQL Prompt代码格式就靠它自动格式化命名是否规范也设计了审查规则。与其嘴上强调“格式要统一”不如把格式化交给机器把人的精力留给逻辑和性能。1.3 规范的适用范围与边界这套规范不是万能的。存储过程从用途上分大致有三类事务型 OLTP 过程、报表查询过程、批量作业过程。三类过程的诉求差别很大——OLTP 过程要求极致的稳定和可控批量作业要求能断点续跑和记录进度报表过程则经常需要接受临时参数组合不能一棍子打死强制一套标准。我在这份规范开头就写明适用范围公司自研业务系统的 DML 操作和常规查询过程统一执行本规范ETL 批量任务、报表平台中的临时脚本可以放宽部分条款但安全类红线不放松。没有这个边界规范很容易在评审会上被以“我这是特殊场景”的理由绕开。先画出边界再谈约束是规范能活下来的前提。2. 命名与结构让代码第一眼就能读2.1 对象命名规则命名是所有人的第一道坎。没有规范时p_GetData、proc_1、sp_abc什么妖魔鬼怪都有。先明确一个大原则禁止使用sp_前缀。原因不是洁癖而是 SQL Server 对以sp_开头的存储过程有一个特殊行为——它会优先去 master 数据库查找该对象。如果本地存在同名过程会有莫名其妙的解析优先级问题而且sp_是系统存储过程的保留命名空间容易撞名轻则混淆重则误调用系统过程。我们内部统一用usp_开头后面跟动宾结构动词放前面比如usp_Insert_Order、usp_Update_CustomerStatus、usp_Get_OrderList。查询类过程不要只写usp_Get_*要把筛选条件带进去比如usp_Get_OrderList_ByStatus。这比proc_查订单这种中英混杂命名强得多至少 grep 代码的时候能一眼知道这个过程是干嘛的。表值函数、视图的命名同样要统一视图用v_表值函数用tvf_标量函数一律建议不要用能用表达式或 CASE 解决的别写标量函数这算是性能上的隐藏红线。触发器用trg_后跟表名和操作比如trg_Order_AfterInsert。2.2 参数与变量的命名参数和变量是存储过程里最容易产生歧义的地方。我们定了三条硬规矩参数以p_开头局部变量以v_开头游标变量以c_开头。归根结底就一个原因——在几百行的过程里看到p_CustomerId就知道这是外部传进来的参数看到v_TotalAmount就知道是临时算出来的局部值不用往上追声明。这成本极其低收益极高。第二条规矩是参数必须带长度尤其是字符类型。p_Name NVARCHAR不写长度是常规坏事——它默认长度是 30但很多开发者以为它是 max配合LEN()或SUBSTRING()使用时会静默截断等到上线才发现数据被切了。我们要求参数必须写成p_Name NVARCHAR(50)所有字符参数的长度要和表结构里的字段定义对齐缺一个长度直接打回。第三条规矩是变量必须初始化。DECLARE v_Flag INT后不赋值直接参与运算初始值是 NULL和 0 完全是两回事。我们的模板里要求所有变量声明行后要么马上赋值要么先赋默认值不允许出现声明后裸奔的情况。这也是为了后续的IF (v_Flag 0)判断不出诡异的 NULL 三值逻辑。2.3 错误编号分配规则SQL Server 的错误号有很多系统保留段50000以下是系统错误使用RAISERROR或THROW时自定义错误一般放在50000以上。但多个人各写各的编号很容易冲突——你抛出 50001 代表“订单不存在”我抛出 50001 代表“库存不足”前端统一捕获时完全错乱。我们内部做了一张错误码分配表按模块划分段位基础模块90000-90099订单模块90100-90199库存模块90200-90299依此类推。每个模块的编号段由模块负责人统一登记新增错误码必须先在 Wiki 错误码表里占位不允许谁都能写。同时在模板里强制用THROW 9xxxx, 描述, 1而不是裸的RAISERROR因为THROW的语法更简单而且THROW不需要msg变量提前声明出错后错误信息里能带上行号排查效率高很多。2.4 脚本文件结构与模板一个存储过程对应一个.sql脚本文件文件名和过程名一一对应比如usp_Insert_Order.sql。禁止一个文件里塞多个过程也禁止用CREATE PROCEDURE不带IF OBJECT_ID(...) DROP的可重复执行保护。我们的文件头部强制使用固定模板包括过程名、作者、创建日期、修改历史、功能描述、输入输出参数说明、依赖的表和函数、以及一个“上次修改原因”的字段。这个头部模板初看挺啰嗦但踩过历史代码的坑就知道值。有一次线上一个过程数据算错了我们靠修改历史里的“2023-05-18 修改过滤条件”这条记录五分钟定位到是上一轮变更引入的。没有这个头你就只能对着 git log 一条一条翻效率差十倍。3. 强制项每一个过程都绕不开的硬性要求3.1 过程头部的固定注释块我们模板里强制设置的注释块长这样/* 过程名 : usp_Insert_Order 功能描述 : 新增订单主表记录并初始化订单日志 作者 : 张工 创建日期 : 2024-03-18 修改历史 : 2024-05-20 李工 增加 p_Remark 为空时的默认值处理 */不要小看这个注释块。有同事抱怨过“写这个浪费五分钟”但实际排查问题时注释块里的修改历史能直接告诉你这段逻辑是谁在什么时候出于什么原因改的配合 git blame 能省数小时。我甚至见过一个近千行的存储过程靠头部注释里的历史记录理出了三代维护者的思路变更轨迹。这是最低成本的知识传承方式。3.2 SET 选项统一设置存储过程开头必须有这样四行SET NOCOUNT ON; SET XACT_ABORT ON; SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON;前两行重点说。SET NOCOUNT ON是为了避免每个 DML 语句都向客户端回传“受影响的行数”这种无用消息否则 EF 等 ORM 在批量执行时可能因为多余的 DONE_IN_PROC token 产生诡异行为SET XACT_ABORT ON的作用是当运行时错误发生时自动回滚当前事务避免半截提交——这在嵌套事务里尤其关键不打开的话某个子语句报错后外层事务可能继续提交数据就烂了。注意一个细节SET XACT_ABORT ON会让部分错误变成不可捕获例如某些编译期错误但对运行时错误来说它才是安全的默认项。我们不接受任何开发者为省事把XACT_ABORT改成 OFF 的理由。3.3 事务与错误处理的标准骨架事务和错误处理是存储过程规范里最核心的部分没有之一。我们内部唯一允许的事务写法是“TRY-CATCH 事务计数器”结构高度模板化BEGIN TRY BEGIN TRANSACTION; -- 业务逻辑 INSERT INTO dbo.Orders(OrderNo, CustomerId, Amount) VALUES (p_OrderNo, p_CustomerId, p_Amount); UPDATE dbo.Stock SET Qty Qty - 1 WHERE SkuId p_SkuId; COMMIT TRANSACTION; END TRY BEGIN CATCH IF TRANCOUNT 0 ROLLBACK TRANSACTION; THROW; -- 重新抛出保留原始错误信息 END CATCH这里有个核心差异不要让开发者在 CATCH 块里自己写RAISERROR拼错误消息直接用THROW把原始错误原样抛出去。很多初级开发者喜欢在 CATCH 里把错误吞掉只记录日志然后返回 0这样前端永远看不到真实错误问题排查难度直接翻倍。我们的规定是日志可以记但记完必须THROW。关于嵌套事务我必须多说一句SQL Server 没有真正的嵌套事务TRANCOUNT只做计数器。两个过程都写了BEGIN TRANSACTIONA 调 BB 里 COMMIT 后TRANCOUNT减一但事务并没有真实提交最终的提交点是外层那个 COMMIT。这就导致很多人误以为“我提交了”其实没有。所以我们规范里明确要求被嵌套调用的子过程里统一不写事务控制事务只允许在最外层入口过程里开启。调用关系一复杂谁开事务谁负责提交责任明确。错误编号使用我们前面说的THROW 9xxxx标准格式。CATCH 里的日志记录我们统一写入dbo.ProcErrorLog表包含错误号、错误消息、过程名、行号、参数快照这些字段全部通过ERROR_NUMBER()、ERROR_MESSAGE()、ERROR_PROCEDURE()等系统函数拿不要自己去拼。3.4 游标允许用但必须有边界我对游标的态度是不禁止但设高门槛。很多时候一条 set-based 的 UPDATE 能解决的事有人偏要用游标一行一行更新跑了 20 分钟还把 tempdb 打满。可是某些场景下游标是合理选择比如需要按顺序处理逐行相关联的业务状态机、或调用外部接口并需要按序补偿的逻辑。规范对游标的使用强制四点必须声明为LOCAL FAST_FORWARD只进、只读的高效模式必须用TRY-CATCH包住并确保CLOSE和DEALLOCATE都在 CATCH 里执行必须在循环体里有一个计数器变量单次执行上限默认 1 万行超出直接抛错退出禁止在循环里做耗时的单行查询也就是不能循环内嵌 SELECT 然后逐行处理一定要先取数据到临时表再循环。这套规矩不是为了限制功能而是逼开发者思考游标循环里的每次迭代是不是在做重复开销能分批处理吗如果答案是不能游标才被允许。3.5 动态 SQL白名单与参数化双保险动态 SQL 是存储过程安全的重灾区。规范没有一刀切禁掉动态 SQL因为某些场景比如动态查询条件、动态表名确实需要但我们有几个强制条件。第一拼 SQL 时值全部用参数化用sp_executesql传参数禁止直接字符串拼接用户输入。第二动态表名、字段名必须用QUOTENAME()包裹这是防 SQL 注入的底线。第三如果动态 SQL 拼装长度超过 4000 个字符要用NVARCHAR(MAX)而不是观望到截断后才发现问题。我还要求所有动态 SQL 组装出的语句在 Debug 模式下能输出到日志表里留痕。发生问题时能直接看到当时生成的实际 SQL快速定位是逻辑错了还是数据变了。4. 参数嗅探与性能红线4.1 参数嗅探问题的正确理解方式先解释清楚SQL Server 在编译存储过程时会基于第一次执行时传入的参数生成执行计划并缓存该计划。后续如果遇到参数分布差距极大的情况缓存计划就可能不适用于当前数据分布——比如某个参数第一次筛选出 5 行计划走索引查找第二次参数筛选出 50 万行同样的计划可能还不如全表扫描快结果过程卡死。处理方式有几种常见的是加OPTION(RECOMPILE)让每次执行都重新生成计划。但RECOMPILE不是银弹它会把每次编译开销都打出来如果是每秒调用几十次的 OLTP 过程CPU 消耗会明显上升。我们的原则是简单短小的过程直接加OPTION(RECOMPILE)收益远大于开销大而复杂的批量过程不要无脑加先考虑用OPTION(OPTIMIZE FOR UNKNOWN)或者把参数赋给局部变量来打破嗅探。这里有个常见误区以为赋给局部变量就万事大吉。实际上 SQL Server 对局部变量使用的是“猜测”的基数估计如果数据分布很不均匀猜测也可能错得离谱。所以我的建议是业务稳定的小过程——RECOMPILE数据分布稳定的大过程——检查统计信息是否正常更新数据倾斜严重的过程——把 SQL 拆成几个分支每个分支用独立的计划。4.2 隐式转换执行计划里的隐形杀手隐式转换是排查慢查询时最容易被忽略的一环。最常见的场景是表字段是VARCHAR存储过程参数传的是NVARCHARSQL Server 为了比较会在字段这边做隐式转换导致字段上的索引完全失效。表现在执行计划里就是多了一个CONVERT_IMPLICIT运算符扫描行数暴涨。我们规范里要求所有过程入参加长度写完整理由之一就是为了减少隐式转换。另一个高发场景是字段类型是CHAR参数传VARCHAR尾随空格规则不同也可能导致查询计划不准确。在评审时看到参数类型和字段类型不一致直接打回不是这条规范苛刻是它带来的性能问题排查成本太高。实际排查技巧是打开执行计划按 CtrlF 搜索CONVERT_IMPLICIT出现即代表有类型不匹配。我们内部发布前自查清单里就有这一条。4.3 临时表和表变量的选择逻辑临时表和表变量是存储过程里最常用的中间存储载体但两者的差异很多人分不清。表变量没有统计信息——对优化器来说它的行数估计永远是 1所以一旦数据量过千行基于表变量的 JOIN 计划大概率是 Nested Loop性能就会崩。临时表有统计信息能支撑更准确的基数估计。但临时表有它的代价写入 tempdb 时有日志和锁开销DDL 要触发隐式编译使用完后要手动 DROP。我们的选择逻辑很直白小于几百行的中间结果用表变量需要多次访问、参与 JOIN、或者要加索引的大中间结果用临时表并在建表后立即创建所需索引。还有一个更隐蔽的点临时表虽然叫“临时”但它会被统计信息自动更新影响如果过程对性能敏感可以在关键大表 JOIN 后加UPDATE STATISTICS或者用OPTION(RECOMPILE)强制重编译来让统计信息生效。4.4 循环改集合逐行改批量这条是性能规范里最朴素但也最容易被突破的一条。我看到过的典型案例一个库存重算过程用游标遍历了两个月 60 万条流水每天凌晨定时跑一次要跑 50 分钟。重构成一条UPDATE ... FROM关联聚合查询后跑完只要 30 秒。这种差距根本不是 SQL Server 的问题是用的人把数据库当成了编程语言的循环容器。规范里的原文写的是凡是可以一次集合操作完成的逻辑禁止使用循环凡是需要循环的优先考虑使用临时表分批处理。具体到实操常见替换思路有用ROW_NUMBER()取每组最新一条替代游标逐行判断用MERGE或UPDATE JOIN替代先查再改用SUM() OVER()替代累计运算循环。这些都是教科书知识但写在规范里反复强调也不嫌多因为实际团队里总有新人再犯。5. 安全与权限存储过程最容易背锅的两个坑5.1 存储过程里的 SQL 注入往往不是直接拼接很多团队以为上了存储过程就没有 SQL 注入问题这个观念错得离谱。注入不止发生在应用层的字符串拼接存储过程内部的动态 SQL 只要拼接了外部输入一样有注入风险。我审过一个过程接收一个排序字段参数代码里直接拼成了ORDER BYp_SortField攻击者传个name DESC; DROP TABLE Orders--后果直接就是删表。就为省一个白名单校验把整个系统送了。我们规范强制任何动态 SQL 中出现的标识符表名、字段名、排序字段只能是代码里写死的白名单不允许直接拼接参数参数值全部走sp_executesql参数化。实现方式很简单——在过程开头定义一个v_AllowedSortFields字符串用CHARINDEX判断传入值是否在列表里不在就抛错返回。多写两三行把风险通道彻底封死。另外一个安全细节是权限的最小化赋值。存储过程执行权限不要直接授予 db_owner正确做法是建一个专门的数据库角色app_executor只授予EXECUTE权限。SQL Server 的存储过程默认有所有权链ownership chaining如果过程和表的 owner 一致登录名只需要过程的EXECUTE权限就能操作表数据不需要直接的 DML 权限这是很多人没意识到的一个设计红利。5.2 使用 EXECUTE AS 和签名解决权限边界有些场景下用户需要执行一个操作但不应拥有底层表权限比如客服人员能调用订单修改过程但不能直接 SELECT 订单表。我们可以用WITH EXECUTE AS让过程以固定账号权限运行但直接指定dbo或sa过于粗暴而且EXECUTE AS一旦固定过程内动态 SQL 可能绕过原调用者权限。更稳妥的方案是用证书为存储过程签名module signing创建一个证书给证书授予所需权限再把证书签名到存储过程上调用者获得的是“签名带来的权限叠加”。这种方式不会改变原始登录名的权限模型又能在需要特权时精准“临时提权”。签名操作虽然配置稍复杂但权限模型完全可控这在涉及敏感数据的过程上我们强制使用。5.3 敏感数据处置与审计留痕存储过程中难免操作手机号、身份证、银行卡这类敏感字段。规范明确过程内部禁止将这些明文数据写入普通日志表如果需要日志字段必须做脱敏处理保留前三位后四位中间的掩码。出错日志表同样不允许含敏感明文排查问题时用参数快照里的脱敏值即可。另外对「修改敏感数据」的操作我们的规范强制在业务表旁边写审计表审计表不设 UPDATE 和 DELETE 权限只允许 INSERT 和 SELECT。触发方式优先用存储过程显式写审计记录而不是依赖触发器——触发器容易在批量操作时放大开销而且难以控制行为。6. 落地工具与审查清单规范不是文档是检查动作6.1 版本管理的配合方式存储过程必须进版本库这是所有规范的前提。我们内部每个存储过程脚本入库时文件路径按模块划分文件名就是对象名。提交信息必须关联需求单号比如feat(order): usp_Insert_Order 增加赠品逻辑 JIRA-3421。这看起来是研发流程但它保证了存储过程变更可以被追溯出现问题时知道是哪个需求哪个改动引来的。一个常见争议是数据库对象要不要走发布流程我的答案是必须走。不能只改测试库然后让 DBA 手工在生产库执行一遍两个环境一旦漂移后面的排查就是灾难。我们用的是简单的发布脚本目录机制发布目录里每个时间戳一个文件夹里面放所有新增或变更的脚本由 DBA 审核后统一执行。虽然比 CI/CD 自动化糙了一点但对小团队够用。6.2 上线前自查清单我整理了一张发布前自查表每次提交存储过程变更前开发者必须逐条对照。表不长但每一条都是血的教训检查项重点内容命名与头部是否符合usp_动宾命名头部注释修改历史是否更新SET 选项是否强制NOCOUNT、XACT_ABORT事务边界子过程是否包含事务最外层是否统一管理错误处理是否有 TRY-CATCHCATCH 是否回滚并THROW隐式转换参数类型和字段类型是否完全一致动态 SQL所有拼接是否参数化标识符是否走白名单游标是否LOCAL FAST_FORWARD是否有上限保护敏感字段日志是否脱敏审计表是否存在权限是否只授予EXECUTE是否有签名提权索引涉及大表时是否确认索引能匹配 SARG 条件说实话清单一开始推行时部分开发会嫌麻烦但只要坚持三个月就能看到效果——被 review 打回的次数明显减少线上存储过程问题数量大幅下降。等形成肌肉记忆清单的勾选就是一种走形式的安全感但形式走多了也就成了习惯。6.3 把规范注入日常Review 与自动检查规范不落到日常动作里就是废纸。我们的做法是代码评审强制绑定存储过程脚本新增或修改存储过程必须由模块 DBA 或资深开发评审通过后才能合并。评审不靠肉眼硬看靠两样东西一是上面的自查清单二是 SQL Prompt 的规则文件。SQL Prompt 可以自定义命名规则、禁止项和格式规则比如禁止sp_前缀、强制SET NOCOUNT ON、禁止SELECT *、强制THROW代替RAISERROR。配置好后保存脚本时自动检查违规项相当于把规范变成了编辑器里的红线提示。这一条最关键因为人的记忆力不可靠机器的检查才可靠。6.4 自动化测试把规则变成回归防线存储过程能不能像普通代码一样有测试完全可以但方式不同。我们用 tSQLt 框架做数据库单元测试针对核心的公共存储过程写测试用例正常入参、边界入参、空值、错误参数、重复提交等场景。测试用例也入版本库跑在测试环境的 CI 流程里。这比想象的简单tSQLt 的测试用例就是一个存储过程断言失败就抛错。比如测usp_Insert_Order先插入一条相同订单号的数据再执行过程断言它是否抛出“订单号重复”的指定错误码。一旦有人改了过程逻辑导致错误码变更测试立刻红掉问题在发版前就被拦住。6.5 从规范到工程师的“肌肉记忆”我前面讲了这么多条条框框最后想聊几句跑偏的风险。规范如果做成教条团队会为了“合规”而写一堆冗余代码。真正的规范应该像交通规则——红灯停绿灯行是底线但什么时候变道、什么时候超车需要结合路况判断。我见过有的同事为了满足“不允许游标”硬把一个批次状态机处理改成三层嵌套子查询维护成本比游标还高。这种为了合规而合规的写法恰恰是规范推广中最大的阻力。所以我在规范里留了一个“例外申请”的口子任何规定都可以打破前提是在代码注释里写明原因并且在评审时通过。这不是给偷懒留后门而是给真正复杂的业务场景留一条逃生通道。一个项目的规范是否健康就看例外申请的数量和理由质量。理由写得好说明规范在约束中逼出了深度思考理由写不出来那基本就是该改代码了。我在实际推动过程中最大的体会是规范的价值不在文档本身而在于它让团队在讨论代码时有了共同语言。两个人 review 同一个存储过程能说出“你这里没按 9.2 条加白名单”和只能说一句“感觉不太安全”沟通成本是完全不同的。如果你也在整理公司内部的 SQL Server 存储过程规范我建议你先别急着写完整手册先从一页纸的强制清单和模板脚本开始跑通一个模块再逐步完善。我见过太多一开始想写“完美规范”的团队最后连初稿都死在 Wiki 里。如果你需要我可以把我们的完整模板脚本和 SQL Prompt 规则文件脱敏后贴出来做参考——不过那些是团队内部沉淀还是留在你们自己的迭代里长出来更有生命力。