SQL Server存储过程实战：从入门到性能优化

简介面向需要掌握SQL编程的数据库初学者和开发人员这份实战示例包通过三个真实业务场景集中展示存储过程与用户自定义函数的实际用法覆盖供应链数据汇总、报表生成与批次号自动分配等典型需求有助于快速理解参数传递、结果返回及预编译语句的执行机制。压缩包体积仅4KB共3个SQL脚本文件可在SSMS中直接打开阅读或修改结构轻盈适合逐行分析存储过程的创建与调用逻辑。示例均取自业务案例可直接套用到订单跟踪、库存盘点或编号生成等模块目前已吸引2015人学习下载对想参考存储过程样例、辨析函数与存储过程差异的读者很有价值。借由这些例子读者可掌握CREATE PROCEDURE定义、EXEC调用、输入输出参数设计并学会将多步骤查询封装为可复用对象从而提升SQL代码的可维护性与安全性。 先交代一个背景。经常有刚接触 SQL Server 的同事或网友问我“有没有存储过程的例子最好能直接跑的那种。”他们大多数不是想研究理论而是手头正好接到一个“把这个逻辑写成存储过程”的需求或者准备面试想快速建立一套能说明白、能写出来的知识框架。也有不少人查过资料但网上例子要么太简单只有一个空的 CREATE PROCEDURE 外壳要么一上来就甩几百行高难度代码根本不知道每一步在干什么。这篇文章我就换一种讲法用一组能直接跑的 SQLSERVER 存储过程例子从建表、入门统计到批量更新、动态 SQL、事务处理再一路讲到真正坑人的参数嗅探、权限问题和执行计划优化。全程用我实际踩坑的经验来串适合拿去做练习、应付面试也适合项目里临时需要上手写存过的人。1. 存储过程是什么先弄清楚它和普通 SQL 的区别1.1 它不是一门语言而是一种封装方式很多人第一次听说存储过程容易被“过程”两个字吓住以为是一门新语言。其实一句话就能说明白把一段经常要用的 SQL 逻辑提前存到数据库服务器里给它起好名字之后再想用直接按名字调用。比如每天要按班级统计考试成绩没有存储过程时你得在应用代码里维护一大段长长的 SQL不仅难看还容易在拼接过程中出错。有了存储过程之后直接EXEC dbo.usp_GetClassScoreStat ClassId 1所有统计逻辑都在服务器端完成应用层只关心传入参数和拿回结果。这里要纠正一个常见的误区存储过程不等于“一定比直接执行 SQL 快”。它确实能减少网络往返也有计划缓存机制但执行计划缓存有时候恰恰是性能问题的来源。关于这一点我会在第 4 节专门演示完整排查过程。你现在只要记住一个核心存储过程的核心价值是复用、封装事务、统一权限入口以及让复杂业务逻辑在数据附近执行而不是单纯为了加速。1.2 什么场景真正离不开存储过程我做过一个订单结算系统那个流程真的让我体会到了存过的必要性。一笔订单结算要做十几步操作校验订单状态、扣减库存、写入流水、修改会员积分。如果每一步都从应用层发一条 SQL光网络往返就是十几次每一步之间还可能因为延迟产生数据不一致。把这套逻辑全部放进存储过程用事务包起来应用层只需要传一个订单号收到成功或失败的返回结果就够了事务一致性由数据库保证。另一个高频场景是权限控制。公司数据分析师需要查询销售明细但不能看到底薪字段。这种情况下把查询逻辑封装进存储过程只给分析师账号分配 EXECUTE 权限底层表权限一个都不给既能满足业务需求又能守住敏感数据。有人会问那为什么不用视图视图适合静态的、单条的查询封装。一旦涉及多步判断、循环、变量、异常处理、事务控制视图就无能为力了。我看到过不少团队用视图套视图去拼复杂业务最后性能雪崩根子就在于选型错了。2. 第一个能跑的示例学生成绩表怎么建、统计存过怎么写2.1 建表思路与字段命名网上经常有人搜“一个学生成绩表如何在 sqlserver 中字段命名”说明很多人卡在了最开始的建表环节。我建议字段命名第一原则是一眼能看出含义风格统一要么 PascalCase要么全小写下划线不要混用。下面这张成绩表够用了CREATE TABLE dbo.StudentScore ( Id INT IDENTITY(1,1) PRIMARY KEY, StudentNo VARCHAR(20) NOT NULL, StudentName NVARCHAR(50) NOT NULL, ClassId INT NOT NULL, SubjectName NVARCHAR(50) NOT NULL, Score DECIMAL(5,2) NOT NULL, ScoreLevel CHAR(1) NULL, ExamDate DATE NOT NULL );有几个细节容易踩坑成绩类型别用 FLOAT因为二进制浮点数会有精度误差成绩这种数据必须精确用 DECIMAL(5,2) 才对学号用 VARCHAR 而不是 INT因为学号可能出现“00123”这种带前导零的值用 INT 存就直接丢了姓名用 NVARCHAR中文存储没压力。2.2 写一个入门存过统计班级平均分和及格率下面这个例子很典型输入班级 ID输出这个班级的参考人数、平均分、最高分、最低分、及格率。代码量不大但几乎包含了存储过程最常用的语法要素参数、变量、聚合函数、SET NOCOUNT ON。CREATE PROCEDURE dbo.usp_GetClassScoreStat ClassId INT AS BEGIN SET NOCOUNT ON; SELECT COUNT(DISTINCT StudentNo) AS StudentCount, AVG(Score) AS AvgScore, MAX(Score) AS MaxScore, MIN(Score) AS MinScore, SUM(CASE WHEN Score 60 THEN 1 ELSE 0 END) * 1.0 / NULLIF(COUNT(*), 0) AS PassRate FROM dbo.StudentScore WHERE ClassId ClassId; ENDSET NOCOUNT ON是必须写的不是可有可无。它用来关闭 SQL Server 返回“受影响的行数”这类无关消息。如果不写在 ADO.NET 或 JDBC 里读结果集时很容易被这些多余消息干扰导致拿到错误的数据。及格率的计算是这段代码里最值得学的部分。它先算出及格人数乘 1.0 转成小数再除以总人数。为什么用NULLIF(COUNT(*), 0)做分母因为如果某个班级一条成绩记录都没有直接除会报“除以零错误”用NULLIF把 0 转成 NULL结果变成 NULL 而不是报错SQL 语句的健壮性就是这样一点一点练出来的。调用方式EXEC dbo.usp_GetClassScoreStat ClassId 1;2.3 存储过程里最常见的类型转换问题搜索词里“sqlserver 字符串转数字”排名很靠前这确实是存储过程开发里特别容易踩的雷。典型场景是应用层把参数以字符串传入存过里要拿去和 DECIMAL 类型比较或做计算。直接CAST是可以但一旦字符串里有空格、逗号、非数字内容整个存过直接报错终止。我习惯的写法是这样DECLARE ScoreStr NVARCHAR(20) N 88.5 ; SET ScoreStr LTRIM(RTRIM(ScoreStr)); IF TRY_CAST(ScoreStr AS DECIMAL(5,2)) IS NULL BEGIN RAISERROR(N成绩格式不正确%s, 16, 1, ScoreStr); RETURN; ENDSQL Server 2012 以后有TRY_CAST转不了就返回 NULL不会直接中断。普通CAST遇到脏数据就抛异常错误信息还不容易定位。所以凡是外部传入的字符串先清洗空格再 TRY 转换再判断结果这一步能省掉后续一大半的麻烦。3. 进阶例码批量更新、动态 SQL 和事务控制3.1 批量更新成绩等级集合思维比游标高效太多从过程式编程转过来的开发写存储过程时第一反应往往是循环——要么用游标要么用 WHILE一行行处理。我只想说能用一条 SQL 解决的批量问题千万不要写循环。数据库最擅长的是集合操作不是逐行处理。下面这个例子根据考试成绩批量生成等级就是典型的集合更新CREATE PROCEDURE dbo.usp_UpdateScoreLevel ExamDate DATE AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; BEGIN TRY BEGIN TRANSACTION; UPDATE sc SET sc.ScoreLevel CASE WHEN sc.Score 90 THEN A WHEN sc.Score 80 THEN B WHEN sc.Score 70 THEN C WHEN sc.Score 60 THEN D ELSE E END FROM dbo.StudentScore AS sc WHERE sc.ExamDate ExamDate; INSERT INTO dbo.ScoreLevelLog(ExamDate, UpdateCount, UpdateTime) SELECT ExamDate, ROWCOUNT, GETDATE(); COMMIT TRANSACTION; END TRY BEGIN CATCH IF XACT_STATE() 0 ROLLBACK TRANSACTION; DECLARE ErrMsg NVARCHAR(MAX) ERROR_MESSAGE(); DECLARE ErrNum INT ERROR_NUMBER(); RAISERROR(N更新成绩等级失败错误编号%d错误信息%s, 16, 1, ErrNum, ErrMsg); END CATCH END这里用一条 UPDATE 配合 CASE WHEN所有学生一次性完成等级更新。SET XACT_ABORT ON是个很容易被忽略的开关它的作用是只要事务里任何语句出错整个事务自动回滚。没有它有些错误发生后事务还挂在那里你手动ROLLBACK都不一定来得及。只要你写涉及事务的存过建议全程打开。ROWCOUNT在这里取得的是刚才 UPDATE 影响的行数也就是真正更新成功多少人再把这个数字写进日志表方便事后核对。3.2 动态 SQL让表名、列名活起来但必须防注入有些业务场景表名没法写死。比如系统按年份分表Score_2024、Score_2025这样查询时根据传入年份拼表名。这个时候只能用动态 SQL。动态 SQL 的标准写法不是字符串直接拼接而是对象名用QUOTENAME值参数走sp_executesqlCREATE PROCEDURE dbo.usp_QueryScoreByYear TableSuffix NVARCHAR(10), Year INT AS BEGIN SET NOCOUNT ON; IF TableSuffix IS NULL OR TableSuffix BEGIN RAISERROR(N年份后缀不能为空, 16, 1); RETURN; END DECLARE Sql NVARCHAR(MAX); SET Sql NSELECT * FROM dbo.Score_ QUOTENAME(TableSuffix) N WHERE YEAR(ExamDate) Year;; EXEC sp_executesql Sql, NYear INT, Year Year; ENDQUOTENAME会把表名安全地包上中括号自动过滤掉可能存在的恶意内容。值类型参数千万不要直接拼进字符串用sp_executesql的参数列表传入这是 SQL 注入的防线。很多人到 Oracle 那边会搜“存储过程 sql语句变量单引号转义”本质就是在动态拼接时字符串值内部还有单引号。SQL Server 这边与其手动转义还不如一律参数化从根上消灭这个问题。3.3 事务里的错误捕获别只记得 ROLLBACK初学者写事务存过最容易犯的错是出了错直接ROLLBACK但没注意当前事务状态。一个严谨的BEGIN CATCH块里IF XACT_STATE() 0这半句是灵魂。XACT_STATE()返回 0 表示当前没有活动事务1 表示事务可以提交-1 表示事务已经进入不可提交状态必须回滚。不加判断就回滚有时候反而会再报一个“没有对应事务”的新错误。错误捕获里还应该记录现场信息。ERROR_MESSAGE()、ERROR_NUMBER()、ERROR_LINE()这些错误函数能告诉你出错在哪一行、错误编号是什么。把这些落进一张错误日志表比只在 CATCH 里弹一个错误信息要专业得多。生产环境排障的时候这些日志能省下你几个小时的时间。另外提醒一个细节RAISERROR的严重级别16 表示用户可修正的普通错误适合业务校验失败之类的情况。不要动不动用 20 以上的高级别那会把整个数据库连接都断掉。4. 一踩一个准的坑存过从快变慢的完整排查链路4.1 场景复现从 100 毫秒变 30 秒问题出在哪有次生产环境一个存储过程白天一直很正常下午突然从 100 毫秒变成 30 秒。常见的错误思路是直接加索引但索引不是万能药。我当时按这个链路一步步查的也推荐你遇到类似问题照这个顺序走第一步先看有没有锁阻塞。执行SELECT * FROM sys.dm_exec_requests WHERE session_id 50;如果看到BLOCKING_SESSION_ID不是 0说明当前请求正被别的会话阻塞。再结合wait_type是LCK_M_X排他锁等待还是PAGEIOLATCH_SH磁盘 IO 等待能快速判断问题性质。第二步抓实际执行计划。在 SSMS 里点击“显示实际执行计划”同时打开SET STATISTICS IO ON和SET STATISTICS TIME ON重新执行一次存储过程。如果逻辑读有几十万而表本身只有几万行那十有八九没走索引在做表扫描。第三步检查统计信息是否过期。执行计划里如果“估计行数”和“实际行数”差了几十倍优先UPDATE STATISTICS后重试。很多“存过突然变慢”的真相不是代码写错了而是统计信息过旧导致优化器选错计划。4.2 参数嗅探为什么同一个存过有时快有时慢参数嗅探是存储过程面试绕不开的话题也是实际开发里最隐蔽的坑。SQL Server 会在第一次执行某个存储过程时根据当时传入的参数值生成执行计划并缓存。后续再执行只要参数值不同也可能直接复用这份旧计划。问题就出在这份计划是针对第一次的参数定制的换一批数据可能完全不合适。举个例子一个订单查询存过第一次传了一个会返回 800 万行的客户 ID优化器觉得全表扫描更划算于是把全表扫描计划缓存了。第二个客户实际只需要 100 行但执行计划还是全表扫描性能自然差到离谱。解决办法要看业务特征。如果存过里 SQL 的筛选列本身随参数变化非常大可以在那条语句上加上OPTION (RECOMPILE)让每次执行都重新编译。这确实会多花一点编译 CPU但换来的通常是更匹配的执行计划。注意不要整个存过所有语句都加只要针对被嗅探影响的那条即可。网上还有一种“局部变量绕嗅探”的写法把参数先赋值给局部变量再查询看着能骗过优化器实际往往让优化器拿到过时的基数估算多数时候帮倒忙。我的建议就是不用。4.3 权限错误和连接层问题报错不一定是 SQL 本身的锅生产环境还有一种常见情况存储过程在 SSMS 里能跑通应用连接却报错。这类问题往往不在 SQL 逻辑而在权限或连接配置。第一类登录账号没执行权限。报错信息经常是“对象名无效”或“权限被拒绝”。解决方案就是单独授权GRANT EXECUTE ON dbo.usp_GetClassScoreStat TO app_user;不要图省事直接给整个库的db_owner。第二类报“调用已取消 0x80041032”这种错误码。多半是命令超时但超时不代表存储过程一定慢也有可能是死锁重试或客户端的 Command Timeout 设置太短。排查时先看服务端执行计划和阻塞情况再决定是否调整客户端超时别一上来就放大超时掩盖问题。第三类动态 SQL 里报“找不到对象”。因为动态 SQL 执行时当前会话的默认架构可能不是dbo所以我在动态 SQL 里会把表名写全成dbo.xxx从根上避免这个歧义。5. 性能好不好用执行计划给存过做个体检5.1 先开两个开关IO 统计和时间统计给存储过程做性能分析我每次都是同一个开场SET STATISTICS IO ON; SET STATISTICS TIME ON; EXEC dbo.usp_GetClassScoreStat ClassId 1;执行完之后SSMS 的“消息”窗口会显示每个表的逻辑读次数、物理读次数以及编译耗时、执行耗时。逻辑读是很直观的指标一个数据页 8KB逻辑读 1000 次相当于这个查询碰了约 8MB 数据。如果最终结果就几百行逻辑读却几十万那基本可以断定索引有问题。5.2 执行计划里最值得看的三个点打开“显示实际执行计划”后别被满屏图标吓到按优先级只看三处就够第一有没有表扫描或聚集索引扫描。小表全表扫描没问题但几百万行的大表出现全扫就是明显的告警信号。第二每个运算符的“估计行数”和“实际行数”差多少。两者差距一旦拉大说明统计信息不准或者优化器估算逻辑有问题这往往是性能拐点所在。第三绿色文本提示。SQL Server 有时候会直接提示“缺少索引”并给出建议索引语句。这个提示有参考价值但别直接照抄要结合查询中的 WHERE 和 JOIN 列来判断是不是真需要。5.3 存过里写可选条件当心这个反模式很多人写查询存过时爱用这种写法WHERE (City IS NULL OR City City)好处是写法简单一个存过能适应所有参数组合。但坏处也很明显优化器为了兼容“参数为 NULL”的可能经常无法有效使用索引最终选择扫描。数据量小无所谓数据量一大性能就崩了。如果这个存过是给报表系统或高频查询用的我更推荐在代码里判断参数是否为空再用 IF 分支写不同的 SQL。虽然代码长一点但每个分支都能稳定走索引。在数据库开发里用代码的冗余换执行计划的稳定是非常划算的买卖。6. 跨语言调用存过的实用经验6.1 C# 调用存储过程参数化是底线应用层调存储过程最常见的错误是把参数直接拼接进 CommandText。比如cmd.CommandText EXEC dbo.usp_GetClassScoreStat ClassId classId;这种写法和 SQL 注入只隔一层纸。正确的做法是使用命令对象和强类型参数using (var conn new SqlConnection(connectionString)) using (var cmd new SqlCommand(dbo.usp_GetClassScoreStat, conn)) { cmd.CommandType CommandType.StoredProcedure; cmd.Parameters.Add(ClassId, SqlDbType.Int).Value classId; conn.Open(); using (var reader cmd.ExecuteReader()) { while (reader.Read()) { Console.WriteLine($平均分: {reader[AvgScore]}); } } }这里特意用了Parameters.Add并明确指定SqlDbType.Int而不是很多人习惯的AddWithValue。AddWithValue虽然省事但经常推断错类型。比如 C# 的 decimal 会默认映射成 decimal(18,0)和存过参数的 DECIMAL(5,2) 不匹配触发隐式转换后索引就失效了。6.2 Kotlin、Java 场景和几个通用提醒如果项目用 Kotlin 或 Java 连 SQL Server走的通常是微软 JDBC 驱动或 JTDS。调用存过时用prepareCall这种写法val call connection.prepareCall({CALL dbo.usp_GetClassScoreStat(?)}) call.setInt(1, classId) val rs call.executeQuery()不管哪种语言有三个点值得记牢。第一存过内部如果有两个 SELECT就会返回两个结果集。应用层读取时记得调用NextResult之类的方法跳到下一个结果集只读第一个是很多人的盲区。第二存过参数名在 SQL Server 里都带前缀但 JDBC 的 CALL 语法里占位符用?按顺序匹配即可。第三执行超时的设置要基于服务端实测不要在没确认执行计划的情况下瞎调大那只是把问题往后拖。最后分享一个我自己的习惯。写存储过程我一定会写注释头用途、作者、修改日期、关键参数说明。以前维护过一个老系统核心存过被前后六个人改过没有注释最后谁也说不清每个参数的含义一个线上问题查了整整两天。存储过程是数据库里的长跑选手可读性差的人迟早要还这笔债。希望这篇文章里的例子和坑能让你在写 SQLSERVER 存储过程的时候少走几段弯路。本文还有配套的精品资源点击获取