简介这份资源面向SQL Server数据库初学者与需要巩固存储过程编写的开发人员围绕存储过程这一核心主题提供可直接参考的实例代码帮助读者理解预编译SQL语句集合在性能优化、代码复用与权限控制方面的实际用法。压缩包共3个文件均为sql脚本整体约4KB体积轻量便于快速查阅其中既有供应链管理场景下的报表类存储过程也包含用于生成下一批次号的用户自定义函数覆盖参数传递、结果返回等典型写法。资源已有2023人学习下载说明其在入门与查阅场景中具有一定参考价值。读者可从中获得存储过程与自定义函数的完整定义范例对照学习CREATE PROCEDURE、EXEC调用及输入参数的使用方式并借鉴业务报表与序列号生成两类实际场景的编写思路为后续掌握事务控制、异常处理等进阶内容打下基础。1. 从一次批量结算卡死说起为什么存储过程值得单独设计某公司做内部结算系统月初跑批时一条更新语句锁了整张明细表前台查询全部排队值班同事盯着活动监视器等了三分钟才等到它自己解开。事后复盘发现问题不在 SQL 写得对不对而在于业务逻辑散落在应用层几十条语句各自开事务、各自提交中间任何一步失败都留下半截数据。这类场景正是存储过程的主场把一组需要原子完成的数据库操作收进一个命名单元由数据库自己保证执行顺序和事务边界。这份 SQLSERVER 存储过程例子面向的读者很明确——正在做数据库课程设计、需要交一份完整数据库方案的学生以及要给内部系统补一套可维护数据访问层的从业者。它不教你 SQL 语法入门而是给出一套能直接照着搭、照着改的存储过程组织方式。2. 需求拆解与总体设计四类存储过程覆盖一套业务闭环2.1 先把需求翻译成存储过程清单拿到“存储过程例子”这个题目最容易翻车的做法是上来就写代码写完发现增删改查各写了一个彼此之间没有关系答辩时被问“你的业务闭环在哪”就答不上来。我一般的做法是先列业务动作再把动作映射成过程。以一个典型的订单与库存场景为例动作可以拆成四类类别典型动作对应存储过程关键约束查询类按条件分页查订单、查库存余量usp_Order_Query、usp_Stock_Get只读、不锁表、支持分页参数写入类下单、扣库存usp_Order_Create单事务、库存不足即回滚批处理类月度结算、超时订单关闭usp_Settle_Monthly分批提交、可重入维护类清理日志、重建索引usp_Maint_CleanLog限定时段、带执行记录这张表就是整个方案的骨架。四类过程职责不同写法也不同查询类追求执行计划稳定写入类追求事务边界清晰批处理类追求可中断可续跑维护类追求可审计。把这张表先定下来后面每个过程该长什么样基本就定了不会出现“写了一堆过程但说不清各自定位”的尴尬。2.2 命名规范与参数约定命名不统一是后期维护的血泪来源。方案里统一采用usp_模块_动作的前缀式命名usp表明是用户存储过程模块名对应业务域动作名用动词开头。参数上定三条硬规矩输入参数一律带前缀且类型与列类型严格一致输出参数统一用RowsAffected INT OUTPUT回传影响行数所有过程第一个可执行语句之前先做参数校验。类型不一致看着是小事实际会让索引失效——比如列是INT而参数传了BIGINTSQL Server 可能走隐式转换执行计划直接从 Seek 退化成 Scan数据量一大就是灾难。2.3 分层结构过程只做编排不做展示总体结构上把存储过程定位成“数据访问层的编排者”而不是“什么活都干的万金油”。应用层负责参数组装和结果展示存储过程负责事务、约束校验和批量操作视图和函数负责可复用的查询片段。这样分层的好处是业务规则变更时只改过程不动应用代码查询逻辑复用时抽成内联表值函数过程里直接JOIN调用比复制粘贴一段 SQL 可靠得多。方案里通常还会约定一个统一的错误码表过程内部用THROW抛出带编号的错误应用层按编号做提示避免把数据库原始报错直接甩给用户。3. 核心存储过程实现从单条写入到批量结算3.1 写入类过程事务、校验与错误处理三件套下单扣库存是最能体现存储过程价值的一段。它要同时完成“插订单头、插订单明细、扣库存、写流水”四步任何一步失败都必须整体回滚。下面这段是方案里的核心实现CREATE OR ALTER PROCEDURE usp_Order_Create OrderNo VARCHAR(32), SkuId INT, Qty INT, RowsAffected INT OUTPUT AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; -- 任何运行时错误自动回滚整个事务 -- 参数校验前置避免带着脏数据进事务 IF Qty IS NULL OR Qty 0 THROW 50001, 下单数量必须大于 0, 1; BEGIN TRY BEGIN TRANSACTION; -- 条件更新扣库存库存不足时影响行数为 0直接判定失败 UPDATE dbo.Stock SET Qty Qty - Qty WHERE SkuId SkuId AND Qty Qty; IF ROWCOUNT 0 THROW 50002, 库存不足或商品不存在, 1; INSERT INTO dbo.OrderHeader (OrderNo, SkuId, Qty, CreatedAt) VALUES (OrderNo, SkuId, Qty, SYSDATETIME()); INSERT INTO dbo.StockFlow (SkuId, ChangeQty, RefOrderNo, CreatedAt) VALUES (SkuId, -Qty, OrderNo, SYSDATETIME()); COMMIT TRANSACTION; SET RowsAffected 1; END TRY BEGIN CATCH IF XACT_STATE() 0 ROLLBACK TRANSACTION; THROW; -- 原样抛出保留错误号供应用层判断 END CATCH END;这段代码有三个设计点值得说清楚。第一SET XACT_ABORT ON让严重级别 16 以上的错误自动回滚省去在 CATCH 里逐条判断的麻烦但 CATCH 里的XACT_STATE()判断仍然保留因为有些错误不会自动回滚属于兜底。第二扣库存用“条件更新 判断ROWCOUNT”而不是“先 SELECT 再判断再 UPDATE”后者在并发下存在检查与执行之间的窗口两个会话可能同时读到足够库存然后都扣成功这是最经典的并发翻车点。第三THROW不带分号结尾在部分版本会报语法错写的时候养成加分号的习惯。3.2 查询类过程分页与参数嗅探的平衡查询类过程最容易写成一坨动态拼接的字符串参数一多就拼出注入风险。方案里统一用参数化分页排序字段通过白名单映射不直接拼进 SQLCREATE OR ALTER PROCEDURE usp_Order_Query PageIndex INT 1, PageSize INT 20, SkuId INT NULL, SortKey VARCHAR(20) CreatedAt AS BEGIN SET NOCOUNT ON; -- 排序字段白名单杜绝把用户输入直接拼进 ORDER BY DECLARE OrderBy VARCHAR(40) CASE SortKey WHEN Qty THEN Qty DESC WHEN CreatedAt THEN CreatedAt DESC ELSE CreatedAt DESC END; SELECT OrderNo, SkuId, Qty, CreatedAt FROM dbo.OrderHeader WHERE (SkuId IS NULL OR SkuId SkuId) ORDER BY CASE WHEN OrderBy Qty DESC THEN Qty END DESC, CASE WHEN OrderBy CreatedAt DESC THEN CreatedAt END DESC OFFSET (PageIndex - 1) * PageSize ROWS FETCH NEXT PageSize ROWS ONLY; END;这里用CASE表达式实现动态排序代价是执行计划可能不如直接拼字符串精确但换来的是计划缓存可复用、无注入风险。数据量在百万级以内这个折中完全够用如果表更大通常的做法是把排序字段拆成几个独立分支每个分支一条静态 SQL让优化器各自生成计划。另外(SkuId IS NULL OR SkuId SkuId)这种写法在参数为 NULL 时可能引起全表扫描方案里建议对高频查询单独建一个不带该条件的版本用应用层判断走哪条别指望一个过程包打天下。3.3 批处理类过程分批提交与可重入月度结算这类过程动辄处理几十万行一次性大事务会把日志撑爆、锁持有时间过长。方案里的做法是循环分批每批固定行数批间提交并用一张执行记录表标记进度保证中断后能续跑CREATE OR ALTER PROCEDURE usp_Settle_Monthly BatchSize INT 5000 AS BEGIN SET NOCOUNT ON; DECLARE Done INT 0; WHILE 1 1 BEGIN BEGIN TRANSACTION; UPDATE TOP (BatchSize) dbo.OrderHeader SET Status Settled WHERE Status Pending AND CreatedAt DATEADD(DAY, -30, SYSDATETIME()); SET Done ROWCOUNT; COMMIT TRANSACTION; IF Done 0 BREAK; -- 没有待处理数据退出循环 -- 批间短暂让出降低对在线查询的影响 WAITFOR DELAY 00:00:00.050; END END;TOP (BatchSize)配合变量在 SQL Server 里是合法的但要注意它和UPDATE ... FROM的写法差异写错会变成更新全表。WAITFOR DELAY那 50 毫秒不是玄学是给其他会话抢锁的机会批处理跑在业务时段时这点让步很值。可重入的关键在于“按状态筛选”而不是“按时间范围筛选”中断后重跑只会处理仍是 Pending 的行不会重复结算。4. 避坑与排查存储过程最容易翻车的五个点4.1 参数嗅探导致计划跑偏现象是同一个过程平时几百毫秒某天突然变成几十秒重启或重编译后又恢复正常。原因是 SQL Server 缓存了首次执行时的参数生成的计划如果首次传入的是极端值比如某个 SKU 只有 3 条记录后续传入大范围参数时复用了不合适的计划。解决办法是在过程内对关键语句加OPTION (RECOMPILE)或者用OPTIMIZE FOR指定代表性参数值。我一般对批处理和报表类过程直接加RECOMPILE代价是每次编译开销换来的是计划稳定。4.2 嵌套事务的假提交现象是内层过程调用了COMMIT外层以为还能回滚结果数据已经落库。原因是 SQL Server 的嵌套事务只有最外层COMMIT才真正提交内层COMMIT只是把TRANCOUNT减一。解决办法是约定“只有最外层过程管事务”被调用的子过程不写BEGIN TRANSACTION或者用SAVE TRANSACTION做保存点。方案里统一规定跨过程调用时事务由入口过程负责子过程只做业务逻辑。4.3 SET NOCOUNT ON 漏写引发的行数误判现象是应用层拿到的受影响行数总是 -1 或者对不上。原因是过程里每条 DML 都会向客户端发送“n 行受影响”的消息客户端驱动可能把它当成结果集处理。解决办法是每个过程开头统一SET NOCOUNT ON需要回传行数时用输出参数显式传递别依赖驱动去数消息。4.4 动态 SQL 拼接带来的注入与计划缓存污染现象是安全扫描报注入风险或者动态 SQL 每次执行都重新编译。原因是直接EXEC(sql)拼接用户输入。解决办法是能用静态 SQL 就不用动态必须动态时用sp_executesql加参数化把变量作为参数传入而不是拼进字符串。这样既防注入又能复用计划缓存。4.5 权限授予过宽现象是应用账号能执行所有过程包括维护类。原因是图省事直接给了db_owner。解决办法是按过程粒度授权GRANT EXECUTE ON dbo.usp_Order_Create TO app_user维护类过程单独授权给运维账号。这一条在课程设计里常被忽略但答辩时被问到“你的权限模型是什么”就是加分项。5. 验收与答辩怎么证明这套存储过程真的能用验收不是把过程跑一遍不报错就完事。我通常按三层来验功能层、并发层、异常层。功能层用一组固定输入跑出预期输出比如库存 10 扣 3 应剩 7扣 11 应报错且库存不变并发层开两个会话同时扣同一 SKU验证不会超卖异常层故意在事务中途制造错误验证回滚干净、无半截数据。这三层各准备三到五个用例写进验收表答辩时直接展示。验收项用例预期结果判定方式正常扣减库存 10扣 3库存 7流水 1 条查表比对库存不足库存 10扣 11报错 50002库存仍 10捕获错误号并发扣减两会话各扣 6仅一个成功库存 4查最终库存中途失败插明细时人为报错订单头不落库查订单表为空批处理续跑结算跑到一半中断再跑不重复结算比对状态计数答辩时最容易被追问的是“为什么用存储过程而不是在应用层写”。回答要落到具体点上事务边界在数据库侧更短、批量操作减少网络往返、权限可以按过程收敛。别泛泛说“性能好”要能说出“一次结算从 N 次往返变成 1 次调用”这种可量化的差别。另一个高频问题是“过程里能不能写业务逻辑”我的观点是校验和编排可以复杂计算和展示逻辑不该进来边界划清楚比一刀切更有说服力。有个习惯我保持了几年每次交付前把每个过程的SET选项、事务写法、错误处理三处逐行过一遍这三处出问题的概率远高于业务 SQL 本身。早期我吃过一次亏一个过程漏了SET XACT_ABORT ON测试环境一切正常上线后遇到死锁直接留下脏数据排查了大半天才定位到。从那以后我每次交方案都强制走一遍这份检查清单宁可多花二十分钟也不留后悔药。希望这套结构能帮到你照着改改就能变成你自己的方案。本文还有配套的精品资源点击获取