简介这份资源面向SQL Server数据库初学者与需要巩固可编程对象的中级开发者围绕存储过程这一核心主题提供可直接参考的实例代码帮助理解预编译SQL语句集合在性能优化、代码复用与权限控制方面的实际价值。压缩包共3个文件均为sql脚本整体约4KB体积轻量便于快速导入查阅其中既有供应链管理场景下的报表与业务处理过程也包含用于生成下一批次号的用户自定义函数覆盖参数传递、结果返回等常见写法。资源已有2023人学习下载说明其在入门与查阅场景中具备一定参考度。读者可从中获取存储过程与函数的建库建表思路、参数定义方式及调用示例对照自身业务改造并借此延伸学习事务控制与异常处理逐步建立对SQL Server可编程对象的整体认识。1. 从一份 SQL Server 存储过程例子说起它到底能帮你省掉哪些重复劳动手上有一批跑了好几年的业务系统报表、对账、批量入账这些逻辑全压在数据库端改一次业务规则就要翻十几个.sql文件这种场景下拿到一份成体系的 SQL Server 存储过程例子价值不在于“多学一个语法”而在于能直接对照着把散落的脚本收敛成可维护的模块。这份资源围绕存储过程展开覆盖创建、参数传递、流程控制、事务与异常处理、游标与批量操作这些高频落点适合两类人一是刚接手数据库端逻辑、需要照着例子把功能跑通的新手二是想把现有脚本重构成规范存储过程、又不想从零设计结构的老手。下面按“先看清结构、再动手复现、最后避开翻车点”的顺序拆。2. 存储过程的基本骨架从 CREATE PROCEDURE 到第一次执行2.1 为什么优先用存储过程而不是拼 SQL 字符串应用层拼 SQL 的老问题很直接参数一多字符串拼接容易漏引号、漏转义遇到NULL和日期格式就翻车同一条业务逻辑在多个接口里复制粘贴改一处漏三处。存储过程把逻辑收进数据库端调用方只传参数、拿结果集网络往返从“多次查询 应用层组装”压成一次调用。常见做法是把批量入账、复杂对账、跨表汇总这类“多语句、强一致”的逻辑放进存储过程把纯查询和简单增删留在应用层。选型上还要看一点存储过程执行计划会被缓存复用参数化查询能减少硬解析但参数嗅探也可能让计划跑偏这个后面避坑章节会展开。2.2 最小可运行例子带输入参数和输出参数的存储过程先跑通一个最朴素的例子理解CREATE PROCEDURE、参数、OUTPUT、EXEC四件事的关系。下面这段按订单号查汇总金额并把笔数通过输出参数带回-- 若已存在同名过程先删除方便反复调试 IF OBJECT_ID(dbo.usp_GetOrderSummary, P) IS NOT NULL DROP PROCEDURE dbo.usp_GetOrderSummary; GO CREATE PROCEDURE dbo.usp_GetOrderSummary OrderNo VARCHAR(32), -- 输入参数订单号 ItemCount INT OUTPUT -- 输出参数明细笔数 AS BEGIN SET NOCOUNT ON; -- 抑制“受影响行数”消息减少网络噪音 SELECT ItemCount COUNT(*) FROM dbo.OrderDetail WHERE OrderNo OrderNo; SELECT OrderNo, SUM(Amount) AS TotalAmount FROM dbo.OrderDetail WHERE OrderNo OrderNo GROUP BY OrderNo; END; GO调用时输出参数必须显式声明变量并加OUTPUTDECLARE cnt INT; EXEC dbo.usp_GetOrderSummary OrderNo SO20240101, ItemCount cnt OUTPUT; SELECT cnt AS ItemCount;逻辑说明SET NOCOUNT ON是高频习惯避免每条语句都往客户端推一行“受影响行数”批量调用时这部分开销不小。ItemCount用OUTPUT修饰调用侧必须用变量承接否则拿不到值。参数命名建议统一加业务前缀避免和列名撞车导致歧义。参数类型要和表字段严格对齐VARCHAR(32)对VARCHAR(64)这种隐式转换会让索引失效属于典型血泪经验。2.3 参数默认值与可选参数的处理方式实际业务里很多查询条件是“传了就过滤、没传就全量”存储过程支持给参数设默认值CREATE PROCEDURE dbo.usp_QueryOrders StartDate DATETIME NULL, EndDate DATETIME NULL, Status TINYINT NULL AS BEGIN SET NOCOUNT ON; SELECT OrderNo, OrderDate, Status, Amount FROM dbo.OrderHeader WHERE (StartDate IS NULL OR OrderDate StartDate) AND (EndDate IS NULL OR OrderDate EndDate) AND (Status IS NULL OR Status Status); END; GO这种(p IS NULL OR col p)的写法可读性好但要注意它属于“万能查询”模式执行计划往往无法针对具体参数组合做最优选择数据量大时容易走全表扫描。常见做法是条件组合固定的场景拆成多个存储过程或者用OPTION (RECOMPILE)让每次调用重新编译代价是编译开销上升。参数默认值只对“调用时未提供该参数”生效显式传NULL和省略参数在语义上等价这点在排查“为什么没过滤”时经常被忽略。3. 流程控制与事务让存储过程扛住批量写入3.1 IF / WHILE / TRY...CATCH 的组合用法存储过程里的流程控制和普通 T-SQL 脚本一致但放进过程后要考虑异常如何向上抛。下面是一个带重试和异常捕获的批量更新骨架CREATE PROCEDURE dbo.usp_BatchUpdateStatus BatchSize INT 500 AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; -- 遇运行时错误自动回滚整个事务 DECLARE Affected INT 1; BEGIN TRY BEGIN TRANSACTION; WHILE Affected 0 BEGIN UPDATE TOP (BatchSize) dbo.OrderHeader SET Status 2 WHERE Status 1; SET Affected ROWCOUNT; END COMMIT TRANSACTION; END TRY BEGIN CATCH IF XACT_STATE() 0 ROLLBACK TRANSACTION; -- 把原始错误信息抛回调用方便于应用层记录 THROW; END CATCH END; GO逻辑说明SET XACT_ABORT ON保证出现严重错误时事务自动回滚避免留下半截数据。WHILE配合TOP (BatchSize)做分批更新是为了避免一次性锁住大量行导致阻塞批量大小按单行更新耗时和锁等待情况调500 到 2000 是常见区间。THROW不带参数会原样重抛当前错误比RAISERROR更简洁但要求 SQL Server 2012 及以上。XACT_STATE()判断事务是否还活着只有非 0 才回滚否则会报“没有对应 BEGIN TRANSACTION”的错。3.2 事务边界该放在存储过程里还是应用层这是设计时最容易吵起来的问题。把事务放在存储过程内部好处是“一次调用即一个原子操作”调用方不用关心内部几条语句坏处是嵌套调用时事务边界不清晰外层再开事务会变成嵌套事务内层COMMIT并不会真正提交。常见做法是单个存储过程内部完成的多表写入事务放在过程内跨多个存储过程或跨服务的业务流程事务放在应用层存储过程只做单步操作并保证自身幂等。判断标准很简单——如果这个存储过程被别的过程调用它就不该自己BEGIN TRANSACTION否则回滚语义会变得难以推理。3.3 用游标还是用集合操作一个可量化的取舍游标在存储过程例子里几乎必出现但它是性能争议最大的部分。逐行处理 10 万行游标可能比集合操作慢一到两个数量级。判断原则能用UPDATE ... FROM、INSERT ... SELECT、窗口函数解决的一律不用游标确实需要“逐行调用另一个存储过程”或“逐行做复杂状态机”时才用游标并且优先选FAST_FORWARD只进只读游标。DECLARE OrderNo VARCHAR(32); DECLARE cur CURSOR FAST_FORWARD FOR SELECT OrderNo FROM dbo.OrderHeader WHERE Status 1; OPEN cur; FETCH NEXT FROM cur INTO OrderNo; WHILE FETCH_STATUS 0 BEGIN EXEC dbo.usp_ProcessSingleOrder OrderNo OrderNo; FETCH NEXT FROM cur INTO OrderNo; END CLOSE cur; DEALLOCATE cur;FAST_FORWARD让优化器可以选择只进只读的最优实现比默认的动态游标轻量。游标用完必须CLOSE加DEALLOCATE否则连接池复用时会残留游标状态。如果这段逻辑每天跑一次、数据量几千行游标的可读性优势可以接受如果数据量上万先想办法改成集合操作这是踩过坑之后的固定动作。4. 避坑与排查存储过程最容易翻车的五个地方4.1 现象改了存储过程执行计划还是旧的原因存储过程首次执行时编译并缓存计划后续修改若只是ALTER PROCEDURE内部逻辑计划可能因对象未重建而沿用旧统计信息。解决用sp_recompile dbo.过程名强制重编译或在过程内对关键语句加OPTION (RECOMPILE)同时确认统计信息是否过期必要时UPDATE STATISTICS。4.2 现象同样的参数有时快有时慢原因参数嗅探。首次编译时的参数值决定了计划形状后续传入差异极大的参数比如从 10 行变成 100 万行会复用不合适的计划。解决对数据分布倾斜明显的查询用局部变量中转参数DECLARE p Input屏蔽嗅探或加OPTION (RECOMPILE)或使用OPTIMIZE FOR指定代表性参数值。三种手段各有代价局部变量会丢失基数估算重编译增加 CPU按查询频率权衡。4.3 现象调用报“参数过多”或“未提供参数”原因参数有默认值时可以省略但一旦某个中间参数省略、后面又用位置传参就会错位。解决调用一律用参数名 值的命名方式不依赖位置输出参数必须带OUTPUT且用变量承接。这条在多人协作的脚本里能省掉大量排查时间。4.4 现象批量插入后事务日志暴涨、日志文件撑满磁盘原因单事务内插入量过大日志在提交前无法截断。解决分批提交每批 1000 到 5000 行批间不共享事务确认恢复模式是否为完整恢复若是且不需要时间点恢复评估是否切换简单恢复模式。分批大小要结合日志增长速度和业务允许的中断窗口来定。4.5 现象存储过程里SELECT返回了多个结果集应用层只拿到第一个原因过程内多条SELECT会依次返回多个结果集部分驱动默认只取第一个。解决明确过程契约——要么只返回一个结果集要么用输出参数回传标量值调试期临时加的SELECT打印语句上线前必须清掉这类残留是线上“结果集错位”的常见来源。5. 进阶技巧用元数据查询把存储过程管起来例子跑通之后真正决定这套东西能不能长期用的是“可发现性”。我习惯在库里留一个巡检脚本定期查存储过程的修改时间、依赖对象和参数定义改版前先跑一遍比翻代码快得多。-- 列出最近 30 天修改过的存储过程 SELECT name, create_date, modify_date FROM sys.procedures WHERE modify_date DATEADD(DAY, -30, GETDATE()) ORDER BY modify_date DESC; -- 查某个存储过程依赖了哪些表 SELECT referenced_entity_name AS DependsOn, referenced_class_desc FROM sys.dm_sql_referenced_entities(dbo.usp_BatchUpdateStatus, OBJECT); -- 查参数定义核对类型是否和表字段一致 SELECT p.name AS ProcName, pa.name AS ParamName, TYPE_NAME(pa.user_type_id) AS DataType, pa.max_length, pa.is_output FROM sys.procedures p JOIN sys.parameters pa ON pa.object_id p.object_id WHERE p.name usp_GetOrderSummary;第一段查改动热点配合版本管理能快速定位“谁在什么时候动了哪个过程”第二段用sys.dm_sql_referenced_entities查依赖重构表结构前先跑避免删列删出运行时错误第三段核对参数类型max_length对VARCHAR是字节数、对NVARCHAR要除以 2这个换算错一次就会误判字段长度。参数类型和表字段不一致引发的隐式转换是执行计划走偏的隐形推手靠这段查询能提前发现。还有一个习惯值得养成每个存储过程头部写一段注释块记录用途、参数含义、返回结果集结构和修改记录。例子里的过程多了之后光靠过程名猜用途迟早会翻车。我一般会在部署脚本里加一步把sys.sql_modules.definition导出成文本归档配合 Git 做 diff改了什么一目了然。从那以后我每次上线前都强制走一遍“依赖查询 参数核对 定义归档”这三步宁可多花十分钟也不在凌晨对着报错日志猜。希望帮到你。本文还有配套的精品资源点击获取
原理与实战解析)
