从网上搜索“SQL Server 存储过程语法”的结果永远是一堆互相抄来抄去的片段这里贴一个CREATE PROCEDURE的骨架那里贴一段IF ELSE的写法再配几个不知道哪个版本才能跑的示例真正用到自己项目里不是缺了错误处理就是踩了版本兼容的坑。所以我干脆把自己整理过无数次、也踩过无数次坑之后沉淀下来的这套笔记做了系统梳理从最基础的创建语法到参数、变量、流程控制、游标、动态SQL、事务处理再到不同版本之间的语法差异全部按实际使用频率和踩坑概率排列。这篇文章适合正在学存储过程的人做系统性的语法参考也适合已经在写但偶尔被某个语法细节卡住的人当速查手册。我尽量不说废话每条语法都给可运行的示例每个示例都标注了适用版本和注意事项保证你拿到SSMS里能直接跑。1. 从最基础的CREATE PROCEDURE开始语法的骨架1.1 一个规范的存储过程应该长什么样很多人接触存储过程是从网上复制一段代码开始的复制得多了就会发现真正的生产环境里存储过程远不止“SELECT * FROM 表”这么简单。我建议你从一开始就按下面的标准骨架来写养成肌肉记忆CREATE OR ALTER PROCEDURE [dbo].[usp_GetUserOrders] UserId INT, OrderStatus NVARCHAR(20) NULL AS BEGIN SET NOCOUNT ON; SELECT o.OrderId, o.OrderNo, o.OrderDate, o.Status, u.UserName FROM dbo.Orders AS o INNER JOIN dbo.Users AS u ON o.UserId u.UserId WHERE o.UserId UserId AND (OrderStatus IS NULL OR o.Status OrderStatus) ORDER BY o.OrderDate DESC; END这个写法里有几个细节值得注意第一CREATE OR ALTER是从SQL Server 2016 SP1开始引入的语法。在2016之前的版本里你要修改一个存储过程只能先DROP PROCEDURE再CREATE PROCEDURE但这样会连权限一起丢而且还存在一个时间窗口——如果过程正在被调用DROP会直接失败。用CREATE OR ALTER就能像改普通代码一样覆盖更新权限关系也不会丢。如果你还在维护2012或2008的老库这条福利享受不到只能用ALTER PROCEDURE。第二SET NOCOUNT ON。这句话的作用是关闭“受影响行数”的消息返回。如果不加每次SELECT或者增删改之后客户端都会收到一个“XX rows affected”的计数消息虽然SSMS里看着无所谓但在应用程序里会多占用网络回包更重要的是某些ORM框架会因为多了这个计数消息而误判结果集导致ExecuteNonQuery返回的行数不对。所以我的习惯是每个存储过程第一行就写SET NOCOUNT ON。第三OrderStatus NVARCHAR(20) NULL这种带默认值的参数是实现“可选筛选条件”最常用的手段。配合(OrderStatus IS NULL OR o.Status OrderStatus)这种写法就能做到“不传这个参数就不过滤”。“变量 IS NULL OR 列 变量”这个模式在查询类存储过程里几乎天天用。1.2 命名规范和属性和系统存储过程的区别存储过程的命名我强烈建议统一用usp_前缀User Stored Procedure虽然微软自家文档里建议不要用前缀避免和系统存储过程混淆但在国内团队协作的实际场景里有前缀反而一眼就能分辨代码里哪些是存储过程调用哪些是表名。真正要避开的是sp_前缀因为在SQL Server的存储过程解析顺序里以sp_开头的存储过程会优先在master数据库里查找如果你创建了sp_MyProc这样的名字每次调用时系统会先检查master库多一次无谓的开销如果恰好和某个系统存储过程重名还会被“劫持”导致奇怪的结果。创建属性方面有几个常见的SET选项需要知道SET NOCOUNT ON已经说过了减少网络消息。SET XACT_ABORT ON如果运行时发生错误整个事务自动回滚不会只回滚出错的那条语句。SET ANSI_NULLS ON/SET QUOTED_IDENTIFIER ON这两个必须在创建时就是ON否则后续插入或更新索引视图、计算列索引时会报错。SSMS生成的脚本默认会带这两行。当你执行CREATE PROCEDURE时SQL Server还会往两个系统视图里写入元数据sys.procedures和sys.sql_modules。前者存的是过程名、创建时间、修改时间、类型等信息后者存的是过程定义的SQL文本。所以想知道库里有哪些存储过程、每个过程的定义是什么可以直接查询这两个视图SELECT p.name AS ProcedureName, p.create_date, p.modify_date, m.definition FROM sys.procedures AS p INNER JOIN sys.sql_modules AS m ON p.object_id m.object_id WHERE p.name LIKE usp_% ORDER BY p.modify_date DESC;这条SQL我很常用尤其是在接手别人维护的老库、或者想快速确认某个过程最近有没有被改过的时候比在SSMS里一个个点开对象资源管理器快得多。1.3 执行存储过程的三种方式写好了存储过程执行方式也和普通查询不一样。最基本的是EXECEXEC dbo.usp_GetUserOrders UserId 1001;这里的规范写法是“参数名 值”。也可以省略参数名直接按位置传值EXEC dbo.usp_GetUserOrders 1001;省略参数名的写法虽然省事但是只要过程定义里参数顺序一变调用方就全错乱了所以我建议在代码和脚本里永远写上参数名成本极低收益是以后调整参数顺序时不必同步改所有调用方。还有一种稍微少见但很实用的方式是EXECUTE ... WITH RECOMPILEEXEC dbo.usp_GetUserOrders UserId 1001 WITH RECOMPILE;这个语法强制SQL Server在每次执行时重新生成执行计划不走缓存。它解决的问题是参数嗅探——有些过程第一次用一个数据分布很特殊的参数值跑了很久生成了一个“歪”的执行计划后面换个完全不同的参数值时还复用那个歪计划性能就炸了。WITH RECOMPILE不是常规手段但排查性能问题时非常好用。2. 参数、变量与流程控制写业务逻辑之前必须搞清楚的细节2.1 输入参数、输出参数和默认值存储过程的参数分三种角色输入参数IN、输出参数OUT以及既是输入又是输出的参数INOUT在T-SQL里通过OUTPUT配合实现。输入参数最常见。值得注意的是SQL Server里的参数默认都是输入参数所以IN关键字可写可不写我习惯不写少打三个字符。输出参数用OUTPUT关键字声明这个在需要从存储过程里拿回单个值或少量值时非常实用。看一个例子CREATE OR ALTER PROCEDURE [dbo].[usp_GetUserTotalAmount] UserId INT, TotalAmount DECIMAL(18, 2) OUTPUT AS BEGIN SET NOCOUNT ON; SELECT TotalAmount SUM(Amount) FROM dbo.Orders WHERE UserId UserId AND Status Paid; -- 如果没有任何订单给个默认值0避免调用方拿到NULL IF TotalAmount IS NULL SET TotalAmount 0; END调用侧必须用OUTPUT关键字接收DECLARE Amount DECIMAL(18, 2); EXEC dbo.usp_GetUserTotalAmount UserId 1001, TotalAmount Amount OUTPUT; SELECT Amount AS TotalAmount;这里的坑在于如果调用侧忘了写OUTPUT过程本身会执行成功但Amount拿到的是初始状态下的值通常是NULL而且SQL Server不会报任何错误这类bug特别隐蔽。我的排查经验是凡是“过程执行了但输出值不对”的问题第一时间检查调用侧的OUTPUT关键字有没有漏。再精确一点输出参数的变量如果是字符串或数值类型用OUTPUT时还可以在前面加OUT二者等价但全写OUTPUT更不容易产生歧义。默认值的写法前面已经出现过OrderStatus NVARCHAR(20) NULL。需要注意带默认值的参数在定义时要放在所有不带默认值参数的后面否则SQL Server会报语法错误。这也是SQL Server比某些编程语言更严格的地方——它不允许你把带默认值的参数放在前面。2.2 DECLARE变量、SET与SELECT赋值的区别在存储过程内部临时变量用DECLARE声明DECLARE CurrentDate DATETIME GETDATE(); DECLARE Count INT; DECLARE UserName NVARCHAR(50);赋值有两种方式SET Count 0;SELECT Count COUNT(*) FROM dbo.Orders WHERE UserId 1001;这两个看着差不多但有个经典区别SET只能给一个变量赋一个标量值SELECT可以从查询结果里取数赋值而且可以同时给多个变量赋值。更重要的是当SELECT查询返回多行时SQL Server会取“最后一行”的值赋给变量这个行为不报错但很容易误导人。如果你只关心唯一值尽量在SELECT赋值时确保查询能返回一行且不返回多行或者加TOP 1配合ORDER BYSELECT TOP 1 UserName UserName FROM dbo.Users WHERE UserId 1001;我见过不少线上事故就是因为这种“多行赋值取最后一行”的SQL改了索引或数据分布后最后一行变了变量值跟着变整个业务逻辑就歪了。所以能确定唯一行就用主键查询确定不了就显式加TOP 1。2.3 IF-ELSE、WHILE、CASE和GOTO存储过程内部控制流最常用的是IF...ELSEIF TotalAmount 10000 BEGIN -- 走VIP逻辑 UPDATE dbo.Users SET UserLevel VIP WHERE UserId UserId; END ELSE IF TotalAmount 5000 BEGIN -- 走普通会员逻辑 UPDATE dbo.Users SET UserLevel Member WHERE UserId UserId; END ELSE BEGIN UPDATE dbo.Users SET UserLevel Normal WHERE UserId UserId; END记住T-SQL的BEGIN...END相当于其他语言里的大括号如果IF下面只有一条语句可以省略BEGIN...END但省略后很容易在后续加语句时踩坑因为SQL Server会把第一条语句作为IF的执行体其他语句变成无条件执行。我见过一个真实的bug原来是IF Flag1 UPDATE ...后来在下面加了一行DELETE ...结果不管Flag等于几删除都会执行。从此我的规矩是只要写了IF下面一律用BEGIN...END包住。WHILE循环在存储过程里主要用于批次处理DECLARE BatchSize INT 1000; DECLARE Processed INT 1; WHILE Processed 0 BEGIN DELETE TOP (BatchSize) FROM dbo.ArchivedOrders WHERE ArchiveDate DATEADD(DAY, -30, GETDATE()) AND Status Closed; SET Processed ROWCOUNT; -- 每删一批歇100毫秒降低对生产环境的影响 WAITFOR DELAY 00:00:00.100; ENDROWCOUNT是上一个语句影响的行数每次执行后要立刻读取因为它会被下一条语句重置。这里用DELETE TOP分批删除就是为了避免一次性删除几十万行把事务日志撑爆、锁表时间过长。这个模式在数据清理归档的存储过程里非常重要。CASE在T-SQL里是表达式不是语句它返回一个值不能用来做流程控制只能用于SELECT列表、WHERE条件或赋值中SELECT OrderNo, CASE WHEN Status Paid THEN 已支付 WHEN Status Pending THEN 待支付 WHEN Status Cancelled THEN 已取消 ELSE 未知 END AS StatusText FROM dbo.Orders;注意CASE表达式里每个WHEN分支的结果类型要一致否则SQL Server会做隐式转换遇到无法转换的值就报错。2.4 表值参数和临时表的配合如果要往存储过程里传一个“列表”在低版本SQL Server里只能拼逗号分隔字符串再拆麻烦且容易出错。从SQL Server 2008开始支持了表值参数Table-Valued Parameter用法分三步第一步创建自定义表类型CREATE TYPE dbo.OrderIdList AS TABLE ( OrderId INT NOT NULL PRIMARY KEY );第二步存储过程里声明参数为这个类型CREATE OR ALTER PROCEDURE dbo.usp_BatchUpdateOrderStatus OrderIds dbo.OrderIdList READONLY, Status NVARCHAR(20) AS BEGIN SET NOCOUNT ON; UPDATE o SET o.Status Status FROM dbo.Orders AS o INNER JOIN OrderIds AS ids ON o.OrderId ids.OrderId; END表值参数必须加READONLY不允许在存储过程中对表值参数做增删改。第三步调用侧构造表值参数传入DECLARE Ids dbo.OrderIdList; INSERT INTO Ids (OrderId) VALUES (1001), (1002), (1003); EXEC dbo.usp_BatchUpdateOrderStatus OrderIds Ids, Status Shipped;表值参数最大的好处是消除了“拆字符串”的环节也避免了动态SQL拼接注入的风险传几千个ID完全没问题。超过一万个的话级别还是有点吃力那种情况我一般会先写入临时表再传表名或者把数据先批量插入正式表再用存储过程处理。3. 游标和动态SQL两个用不好就容易“翻车”的进阶语法3.1 游标什么时候真的必须用SQL是一个面向集合的语言用游标逐行处理数据在绝大多数场景下都是反模式性能比集合操作低一个量级。我在代码评审里看到DECLARE CURSOR就会条件反射地警觉。但游标并不是一无是处有些场景确实绕不开需要对每一行做一次独立的存储过程调用且调用的参数就是该行数据需要在循环里对同一行的多列做复杂的、依赖前一行结果的逐步计算需要对一张大表的每一行执行一次不一样的DML操作且无法用CASE或JOIN表达。如果你只是“想逐行处理”那大概率是没找到写集合操作的正确姿势。真正需要游标时标准写法是这样的DECLARE OrderId INT; DECLARE OrderNo NVARCHAR(50); DECLARE cur_orders CURSOR LOCAL FAST_FORWARD FOR SELECT OrderId, OrderNo FROM dbo.Orders WHERE Status Pending ORDER BY OrderDate; OPEN cur_orders; FETCH NEXT FROM cur_orders INTO OrderId, OrderNo; WHILE FETCH_STATUS 0 BEGIN -- 每行业务处理逻辑比如调用另一个存储过程 EXEC dbo.usp_ProcessSingleOrder OrderId OrderId; FETCH NEXT FROM cur_orders INTO OrderId, OrderNo; END CLOSE cur_orders; DEALLOCATE cur_orders;重点说几个细节第一LOCAL FAST_FORWARD。LOCAL表示游标只在当前批处理内有效用完自动销毁避免把游标定义成全局游标污染会话FAST_FORWARD告诉SQL Server这个游标只能向前滚动并且启用优化路径性能最好。如果只是顺序扫描不要用SCROLL或DYNAMIC那些会带来额外的锁和性能开销。第二FETCH_STATUS在每次FETCH之后会被更新值为0表示成功-1表示超出结果集末尾-2表示取到的是被删除的行。循环判断依据它。第三CLOSE和DEALLOCATE的区别CLOSE是关闭游标释放当前结果集但游标本身还在可以OPEN重新打开DEALLOCATE是彻底释放游标占用的资源。写完整的最佳实践是处理完就CLOSE如果接下来不再用就顺手DEALLOCATE。还有一个容易忽略的点游标的性能不只是“慢”还会占用锁资源。游标默认是READ_ONLY的加上FAST_FORWARD后锁粒度会比较轻。如果你需要可更新游标建议加上SCROLL_LOCKS或OPTIMISTIC中的一种不要用默认的动态游标很容易出现并发冲突。3.2 动态SQL的正确打开方式EXEC vs sp_executesql动态SQL在存储过程里很常用比如表名或列名不能作为参数直接传入或者要动态拼接筛选条件。T-SQL里执行动态SQL有两条路EXEC (SELECT * FROM dbo.Orders WHERE OrderId OrderId);EXEC sp_executesql NSELECT * FROM dbo.Orders WHERE OrderId OrderId, NOrderId INT, OrderId OrderId;最核心的差别是前者是字符串拼接后者是参数化查询。参数化除了能防止SQL注入还有一个非常关键的好处——它生成的执行计划可以被参数值不同的调用复用减少编译开销降低CPU压力。用EXEC拼接动态SQL的经典反面教材DECLARE SQL NVARCHAR(MAX); DECLARE UserId INT 1001; SET SQL SELECT * FROM dbo.Orders WHERE UserId CAST(UserId AS NVARCHAR(20)); EXEC (SQL);这个写法在真实系统里非常危险。用户的输入一旦拼进SQL黑客构造一个UserId 1 OR 11就能把整张表拖走还能用EXEC xp_cmdshell之类的方式提权。虽然SQL注入已经是老生常谈但我这两年做安全排查时依然能在内部系统里翻出这种代码。使用sp_executesql的参数化写法DECLARE SQL NVARCHAR(MAX); DECLARE UserId INT 1001; SET SQL NSELECT * FROM dbo.Orders WHERE UserId UserId; EXEC sp_executesql SQL, NUserId INT, UserId UserId;注意字符串前面的NNVARCHAR类型字符串必须加N前缀否则中文字符会乱码。这个我以前吃过亏在存储过程中拼中文的WHERE条件时忘了写N所有匹配项全部查不到排查了大半天才发现是参数声明里少了N。3.3 动态SQL里最常犯的三个错误第一个是权限问题。存储过程本身被授权执行但存储过程中的动态SQL是在调用者的安全上下文里执行的如果调用者没有访问目标表的权限动态SQL就会报错。解决方法是给存储过程开EXECUTE AS或者用证书签名但这属于高级用法一般不建议滥用宁可让DBA显式授权。第二个是字符串拼接时的引号转义。如果SQL里需要拼入字符串字面量单引号要写两次DECLARE Status NVARCHAR(20) Paid; DECLARE SQL NVARCHAR(MAX); SET SQL NSELECT * FROM dbo.Orders WHERE Status Status ; EXEC (SQL);这个写法很容易数错引号层级初学者一脸懵。所以只要能用参数传入的一定要用sp_executesql的参数列表不要拼字符串。参数化之后单引号问题就不存在了。第三个是NVARCHAR(MAX)的长度截断。动态SQL拼接时如果变量定义为NVARCHAR(4000)SQL会被截断到4000字符而导致语法错误。现在新代码我一律写NVARCHAR(MAX)省得以后SQL太长被截出莫名其妙的问题。4. 错误处理与事务生产环境的存储过程必须写TRY...CATCH4.1 使用TRY...CATCH捕获异常T-SQL的错误处理和C#、Java里类似用BEGIN TRY和BEGIN CATCH包住可能出错的逻辑CREATE OR ALTER PROCEDURE dbo.usp_CreateOrder UserId INT, ProductId INT, Quantity INT, Amount DECIMAL(18, 2) AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; DECLARE OrderId INT; BEGIN TRY BEGIN TRANSACTION; INSERT INTO dbo.Orders (UserId, ProductId, Quantity, Amount, Status, CreateDate) VALUES (UserId, ProductId, Quantity, Amount, Pending, GETDATE()); SET OrderId SCOPE_IDENTITY(); UPDATE dbo.Products SET StockQty StockQty - Quantity WHERE ProductId ProductId; COMMIT TRANSACTION; SELECT OrderId AS OrderId; END TRY BEGIN CATCH IF TRANCOUNT 0 ROLLBACK TRANSACTION; DECLARE ErrorMessage NVARCHAR(4000) ERROR_MESSAGE(); DECLARE ErrorSeverity INT ERROR_SEVERITY(); DECLARE ErrorState INT ERROR_STATE(); RAISERROR(ErrorMessage, ErrorSeverity, ErrorState); END CATCH END这个例子是很经典的“先插入主表、再扣库存”的业务场景必须保证两条语句要么都成功要么都失败。TRY...CATCH捕获错误后第一时间检查TRANCOUNT并回滚事务防止事务悬挂。ERROR_MESSAGE()、ERROR_SEVERITY()、ERROR_STATE()是CATCH块里能直接调用的系统函数。还有ERROR_NUMBER()和ERROR_LINE()ERROR_LINE()能告诉你是存储过程里第几行出错这个对排查问题非常有用。4.2 XACT_ABORT和事务回滚的细节单独说下SET XACT_ABORT ON。如果没有这句很多运行时错误比如字符串截断、除零错误、约束冲突只违反当前一条语句但不会终止整个批处理和事务。后果是你的事务只回滚了出错的那条语句之前已经INSERT/UPDATE的数据全留在库里而CATCH块里若再执行ROLLBACK就会碰到一个很迷惑的现象——“事务已经在语句级回滚了”。举个例子你在事务里先插入一条订单然后更新库存时出现字符串截断错误如果没有XACT_ABORT ONINSERT不会回滚库存更新失败事务处于“能提交但有逻辑缺失”的状态。如果后续代码判断出错再回滚事务才整个消失。很多线上“脏数据”就是这么来的。所以我的铁律是存储过程只要涉及多语句写操作开头就加SET XACT_ABORT ON配合TRY...CATCH这样才能保证“出错即整体回滚”。4.3 嵌套事务和保存点T-SQL里的BEGIN TRANSACTION是可以嵌套的但TRANCOUNT不是真正的嵌套——内层COMMIT只是让TRANCOUNT减1外层COMMIT才是真正的提交点。很多人以为嵌套事务能实现部分回滚这是典型的误区。如果确实只想回滚部分操作就要用保存点SAVE TRANSACTIONBEGIN TRY BEGIN TRANSACTION; -- 第一批操作 INSERT INTO dbo.Orders ...; SAVE TRANSACTION OrderInserted; -- 第二批操作可能出错 UPDATE dbo.Products SET StockQty StockQty - Quantity WHERE ProductId ProductId; COMMIT TRANSACTION; END TRY BEGIN CATCH IF TRANCOUNT 0 BEGIN ROLLBACK TRANSACTION OrderInserted; COMMIT TRANSACTION; END -- 记录错误日志后重新抛出 INSERT INTO dbo.ErrorLog (ErrorMessage, ErrorLine, ErrorTime) VALUES (ERROR_MESSAGE(), ERROR_LINE(), GETDATE()); END CATCH保存点允许你只回滚到某个点而不是回滚整个事务。这个技术在复杂的多阶段处理里很有用但一般业务逻辑我建议尽量简单化能用集合操作绝不上这么复杂的控制流。4.4 错误日志与RAISERROR/THROW生产环境的存储过程光有TRY...CATCH不够还需要把错误记录下来方便事后查日志。我习惯在CATCH块里同时做两件事把错误插入日志表然后RAISERROR或THROW把错误重新抛给应用层。RAISERROR是老语法THROW是SQL Server 2012引入的新语法THROW 50001, 订单创建失败, 1;THROW更简洁还可以不带参数直接写在CATCH里表示原样重新抛出原始错误BEGIN CATCH INSERT INTO dbo.ErrorLog (...) VALUES (...); THROW; END CATCH需要注意THROW无法捕获时终止会话中的批处理所以调用方的TRY...CATCH会收到异常。我们经常在存储过程的CATCH块里写THROW来保留最原始的错误信息而不用重新拼一个错误文本。4.5 错误处理的常见坑第一个坑是CATCH块里执行了会引发新错误的语句导致原始错误信息丢失。比如CATCH里再写一条不合法的INSERT整个会话会抛出新错误原始的被覆盖。处理方式是把错误日志插入操作也包一层TRY...CATCH或者用SAVE TRANSACTION包裹。第二个坑是RAISERROR的严重级别。用11~16会返回普通异常19以上默认是系统级别错误会导致客户端连接断开。一般不要用20以上的级别。第三个坑是事务和CATCH的位置。如果事务在TRY外面开始CATCH块里的ROLLBACK会失败因为CATCH上下文里看不到最外层事务的语句这种情况SQL Server会抛“当前事务无法提交”的错误。5. 不同SQL Server版本下的语法差异与兼容性5.1 版本演进对存储过程语法的影响很多公司还在用SQL Server 2008 R2、2012、2014而且这些老版本的生命周期早已停止更新只是业务还在跑。如果你写的存储过程要兼容多个版本语法选择就必须谨慎。这里梳理几个我踩过版本坑的语法点5.2 老版本缺失的实用语法DROP IF EXISTSSQL Server 2016才支持。2008-2014里只能这么写IF OBJECT_ID(dbo.usp_GetUserOrders, P) IS NOT NULL DROP PROCEDURE dbo.usp_GetUserOrders;STRING_AGGSQL Server 2017才支持。老版本要把多行拼成逗号字符串只能靠FOR XML PATHSELECT STUFF(( SELECT , UserName FROM dbo.Users WHERE DepartmentId DeptId ORDER BY UserName FOR XML PATH() ), 1, 1, );这个写法是现代SQL Server里90%字符串聚合需求的解法虽然看着绕但在2012/2014上它就是标准答案。注意变量别用错类型要声明为NVARCHAR(MAX)防止截断。OFFSET-FETCH分页SQL Server 2012才支持。2008里只能用ROW_NUMBER()配合BETWEEN实现;WITH Paged AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY OrderId DESC) AS RowNum FROM dbo.Orders ) SELECT * FROM Paged WHERE RowNum BETWEEN 1 AND 20;2012直接用SELECT * FROM dbo.Orders ORDER BY OrderId DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;注意OFFSET-FETCH必须要有ORDER BY没有ORDER BY会直接报错。IIF、CONCAT2012引入。2016还引入了DROP IF EXISTS、STRING_SPLIT2017引入了TRIM这些都是写新存储过程时很顺手但老库用不了的功能。如果你的数据库版本在2016以下用CONCAT时要注意它不接受NULL会返回空字符串和拼接时NULL照样是NULL的行为完全不同。JSON函数SQL Server 2016开始支持OPENJSON、JSON_VALUE。如果要在2008/2012里处理JSON就只能用CLR或动态SQL非常痛苦。5.3 兼容级别的坑数据库的兼容级别compatibility level决定了它支持哪些语法。比如数据库兼容级别是100对应2008即使你装在SQL Server 2019的实例上用STRING_AGG还是会报“不是可识别的内置函数名称”。这个坑特别容易忽略因为SSMS连上后看起来是2019但SELECT compatibility_level FROM sys.databases WHERE name 你的库查出来可能是100或110。如果需要用新函数可以改兼容级别ALTER DATABASE [YourDB] SET COMPATIBILITY_LEVEL 150; -- 对应SQL Server 2019这个操作要评估影响面改兼容级别可能改变部分查询优化器行为不是顺手就能改的。在多个版本共存的开发环境里我一般会让开发库和生产库的兼容级别保持严格一致避免“开发能跑生产报错”。5.4 SQL Server 2022里新增的几个实用函数如果你的项目已经用上SQL Server 2022几个新函数对存储过程写法帮助很大DATETRUNC(datepart, date)按指定精度截断时间比如DATETRUNC(month, GETDATE())取当月第一天比之前的DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)好懂太多。GREATEST和LEAST在一组值里取最大/最小省去嵌套CASE WHEN。STRING_SPLIT增加了ordinal参数可以返回分割后每个元素的下标老版本里只能顺序无序号。IS [NOT] DISTINCT FROM处理NULL的比较NULL IS DISTINCT FROM 1返回true这让WHERE条件里写OrderStatus IS NOT DISTINCT FROM o.Status很方便比(OrderStatus IS NULL OR o.Status OrderStatus)简洁。但是注意用这些新函数前先确认目标环境的SQL Server版本和兼容级别否则一到部署环节就暴露兼容问题轻则DBA驳回脚本重则生产发布失败。6. 存储过程调优与常见坑基于实际排障的经验总结6.1 参数嗅探问题为什么同样的过程时快时慢存储过程第一次执行时SQL Server会根据传入的参数值生成执行计划并缓存在计划缓存里后续调用默认复用这个计划。问题是如果第一次传了一个数据分布极端的参数值比如查一个占总数据量1%的ID优化器会生成一个针对“少量数据”的查询计划后面再传一个占总数据量50%的ID时SQL Server还是复用那个“少量数据”的计划就可能出现全表扫描或者严重的行估计差异表现为同一个存储过程“有时秒回有时死慢”。这是存储过程性能问题里最经典的“参数嗅探”。常用解法有这几种加OPTION (RECOMPILE)每次执行都重新生成计划适合执行频率低但单次操作重的过程代价是每次都有编译开销。加OPTION (OPTIMIZE FOR (参数 某典型值))让优化器按指定的典型参数值生成计划。适合参数分布稳定、可以找到“中庸”值的场景。在局部变量里复制参数再用于查询屏蔽原始参数值的影响。使用WITH RECOMPILE执行即调用侧临时重编译。实际排障时我会先看执行计划里Estimated Number of Rows和Actual Number of Rows差多少差出两个数量级以上基本就是参数嗅探或统计信息过期。6.2 临时表vs表变量选择不对性能差十倍存储过程里经常需要中间结果集。临时表#temp和表变量table都常用但特性差异很大对比项临时表 #temp表变量 table统计信息有自动更新无只有表定义时的大小估计索引可创建主键和索引可创建主键和约束索引能力较弱作用域当前会话可被子过程访问当前批次不能被子过程访问事务回滚参与事务可回滚不参与事务回滚不影响表变量中的数据适用场景数据量大、需要JOIN复杂查询数据量小、传参、临时缓存我自己的经验法则是数据行数超过几百行且后续要多表JOIN用#临时表只有几行或纯粹当列表传参用表变量。在优化慢存储过程时把表变量换成临时表往往执行时间直接降一个数量级因为优化器对临时表有统计信息能估算准确行数。6.3 隐式转换导致的索引失效存储过程中最常见的坑之一是参数类型和列类型不一致导致的隐式转换。比如列OrderNo是NVARCHAR(50)你在WHERE里写OrderNo 100123整数SQL Server会把列类型转换成整数导致该列上的索引无法正确利用。-- 错误OrderNo是NVARCHAR参数却是INT WHERE OrderNo OrderIdINT-- 正确参数类型与列类型一致 WHERE OrderNo OrderNoNVARCHAR排查方法很简单看执行计划里有没有CONVERT_IMPLICIT这个运算符只要出现它就说明有隐式转换在阻碍索引利用。生产环境里很多慢查询其实就是隐式转换造成的把参数类型改成和列一致问题瞬间消失。6.4 不要在存储过程里用标量函数存储过程里调用自定义标量函数Scalar Function往往会导致查询无法并行、每行都要调用一次在大数据量下性能极差。尤其是嵌套在SELECT列表或WHERE条件里时代价更大。比如这个写法每查一行就要把dbo.ufn_GetUserLevel(UserId)跑一遍SELECT UserName, dbo.ufn_GetUserLevel(UserId) AS UserLevel FROM dbo.Users;同样的逻辑能用CASE、LEFT JOIN或内联表值函数就不要用标量函数。SQL Server 2019之后的标量函数内联化Scalar UDF Inlining特性可以在一定条件下自动优化但依赖版本和函数复杂度不可控最好还是自己在设计阶段就避开。6.5 NOLOCK到底能不能用WITH (NOLOCK)在存储过程里经常出现很多人把它当成性能优化神器。它实际上使用的是“脏读”语义——可以读取到其他事务未提交的数据而且可能读到重复行或跳过行。对于报表类、对一致性要求不高的查询场景NOLOCK确实能减少阻塞但对于订单、库存这类强一致性业务用了NOLOCK出现“读到未提交的库存扣减”这种问题后果很严重。我的建议是在线交易类存储过程坚决不用NOLOCK偏查询统计的过程如果阻塞严重优先考虑开启READ COMMITTED SNAPSHOT隔离级别而不是简单粗暴地加NOLOCK。6.6 哪些场景不应该用存储过程最后说个反常识的体会不是所有逻辑都适合塞进存储过程。用了存储过程代码版本控制、单元测试、跨数据库迁移都会增加额外的成本。如果只是简单CRUD用ORM直接操作还更直观。如果要做非常复杂的报表查询有时候用视图或直接写查询SQL都比存储过程灵活。我这些年维护老系统最大的感受是存储过程适合做“接口稳定、逻辑相对固定、需要进行权限控制或减少网络往返”的场景而频繁变化的业务规则、需要大规模重构的逻辑勉强用存储过程实现只会让你维护得想离职。所以把存储过程当工具而不是信仰才是正确的态度。最后再分享一个我在实际项目里养成的习惯写完存储过程不要直接结束养成习惯执行一下sp_helptext或者直接查sys.sql_modules把过程定义导出到版本控制里和代码一起走评审和发布。很多团队数据库脚本没有纳入Git管理生产环境的过程和开发环境不一致出了事故DBA改完又没人知道后果就是下次上线直接覆盖生产修复。把存储过程脚本当成一等公民纳入版本管理、做自动化部署虽然前期麻烦点但长期来看能省掉无数个“生产为什么和开发不一样”的抓狂夜晚。 SQL Server存储过程语法详解：从CREATE PROCEDURE到性能调优
从网上搜索“SQL Server 存储过程语法”的结果永远是一堆互相抄来抄去的片段这里贴一个CREATE PROCEDURE的骨架那里贴一段IF ELSE的写法再配几个不知道哪个版本才能跑的示例真正用到自己项目里不是缺了错误处理就是踩了版本兼容的坑。所以我干脆把自己整理过无数次、也踩过无数次坑之后沉淀下来的这套笔记做了系统梳理从最基础的创建语法到参数、变量、流程控制、游标、动态SQL、事务处理再到不同版本之间的语法差异全部按实际使用频率和踩坑概率排列。这篇文章适合正在学存储过程的人做系统性的语法参考也适合已经在写但偶尔被某个语法细节卡住的人当速查手册。我尽量不说废话每条语法都给可运行的示例每个示例都标注了适用版本和注意事项保证你拿到SSMS里能直接跑。1. 从最基础的CREATE PROCEDURE开始语法的骨架1.1 一个规范的存储过程应该长什么样很多人接触存储过程是从网上复制一段代码开始的复制得多了就会发现真正的生产环境里存储过程远不止“SELECT * FROM 表”这么简单。我建议你从一开始就按下面的标准骨架来写养成肌肉记忆CREATE OR ALTER PROCEDURE [dbo].[usp_GetUserOrders] UserId INT, OrderStatus NVARCHAR(20) NULL AS BEGIN SET NOCOUNT ON; SELECT o.OrderId, o.OrderNo, o.OrderDate, o.Status, u.UserName FROM dbo.Orders AS o INNER JOIN dbo.Users AS u ON o.UserId u.UserId WHERE o.UserId UserId AND (OrderStatus IS NULL OR o.Status OrderStatus) ORDER BY o.OrderDate DESC; END这个写法里有几个细节值得注意第一CREATE OR ALTER是从SQL Server 2016 SP1开始引入的语法。在2016之前的版本里你要修改一个存储过程只能先DROP PROCEDURE再CREATE PROCEDURE但这样会连权限一起丢而且还存在一个时间窗口——如果过程正在被调用DROP会直接失败。用CREATE OR ALTER就能像改普通代码一样覆盖更新权限关系也不会丢。如果你还在维护2012或2008的老库这条福利享受不到只能用ALTER PROCEDURE。第二SET NOCOUNT ON。这句话的作用是关闭“受影响行数”的消息返回。如果不加每次SELECT或者增删改之后客户端都会收到一个“XX rows affected”的计数消息虽然SSMS里看着无所谓但在应用程序里会多占用网络回包更重要的是某些ORM框架会因为多了这个计数消息而误判结果集导致ExecuteNonQuery返回的行数不对。所以我的习惯是每个存储过程第一行就写SET NOCOUNT ON。第三OrderStatus NVARCHAR(20) NULL这种带默认值的参数是实现“可选筛选条件”最常用的手段。配合(OrderStatus IS NULL OR o.Status OrderStatus)这种写法就能做到“不传这个参数就不过滤”。“变量 IS NULL OR 列 变量”这个模式在查询类存储过程里几乎天天用。1.2 命名规范和属性和系统存储过程的区别存储过程的命名我强烈建议统一用usp_前缀User Stored Procedure虽然微软自家文档里建议不要用前缀避免和系统存储过程混淆但在国内团队协作的实际场景里有前缀反而一眼就能分辨代码里哪些是存储过程调用哪些是表名。真正要避开的是sp_前缀因为在SQL Server的存储过程解析顺序里以sp_开头的存储过程会优先在master数据库里查找如果你创建了sp_MyProc这样的名字每次调用时系统会先检查master库多一次无谓的开销如果恰好和某个系统存储过程重名还会被“劫持”导致奇怪的结果。创建属性方面有几个常见的SET选项需要知道SET NOCOUNT ON已经说过了减少网络消息。SET XACT_ABORT ON如果运行时发生错误整个事务自动回滚不会只回滚出错的那条语句。SET ANSI_NULLS ON/SET QUOTED_IDENTIFIER ON这两个必须在创建时就是ON否则后续插入或更新索引视图、计算列索引时会报错。SSMS生成的脚本默认会带这两行。当你执行CREATE PROCEDURE时SQL Server还会往两个系统视图里写入元数据sys.procedures和sys.sql_modules。前者存的是过程名、创建时间、修改时间、类型等信息后者存的是过程定义的SQL文本。所以想知道库里有哪些存储过程、每个过程的定义是什么可以直接查询这两个视图SELECT p.name AS ProcedureName, p.create_date, p.modify_date, m.definition FROM sys.procedures AS p INNER JOIN sys.sql_modules AS m ON p.object_id m.object_id WHERE p.name LIKE usp_% ORDER BY p.modify_date DESC;这条SQL我很常用尤其是在接手别人维护的老库、或者想快速确认某个过程最近有没有被改过的时候比在SSMS里一个个点开对象资源管理器快得多。1.3 执行存储过程的三种方式写好了存储过程执行方式也和普通查询不一样。最基本的是EXECEXEC dbo.usp_GetUserOrders UserId 1001;这里的规范写法是“参数名 值”。也可以省略参数名直接按位置传值EXEC dbo.usp_GetUserOrders 1001;省略参数名的写法虽然省事但是只要过程定义里参数顺序一变调用方就全错乱了所以我建议在代码和脚本里永远写上参数名成本极低收益是以后调整参数顺序时不必同步改所有调用方。还有一种稍微少见但很实用的方式是EXECUTE ... WITH RECOMPILEEXEC dbo.usp_GetUserOrders UserId 1001 WITH RECOMPILE;这个语法强制SQL Server在每次执行时重新生成执行计划不走缓存。它解决的问题是参数嗅探——有些过程第一次用一个数据分布很特殊的参数值跑了很久生成了一个“歪”的执行计划后面换个完全不同的参数值时还复用那个歪计划性能就炸了。WITH RECOMPILE不是常规手段但排查性能问题时非常好用。2. 参数、变量与流程控制写业务逻辑之前必须搞清楚的细节2.1 输入参数、输出参数和默认值存储过程的参数分三种角色输入参数IN、输出参数OUT以及既是输入又是输出的参数INOUT在T-SQL里通过OUTPUT配合实现。输入参数最常见。值得注意的是SQL Server里的参数默认都是输入参数所以IN关键字可写可不写我习惯不写少打三个字符。输出参数用OUTPUT关键字声明这个在需要从存储过程里拿回单个值或少量值时非常实用。看一个例子CREATE OR ALTER PROCEDURE [dbo].[usp_GetUserTotalAmount] UserId INT, TotalAmount DECIMAL(18, 2) OUTPUT AS BEGIN SET NOCOUNT ON; SELECT TotalAmount SUM(Amount) FROM dbo.Orders WHERE UserId UserId AND Status Paid; -- 如果没有任何订单给个默认值0避免调用方拿到NULL IF TotalAmount IS NULL SET TotalAmount 0; END调用侧必须用OUTPUT关键字接收DECLARE Amount DECIMAL(18, 2); EXEC dbo.usp_GetUserTotalAmount UserId 1001, TotalAmount Amount OUTPUT; SELECT Amount AS TotalAmount;这里的坑在于如果调用侧忘了写OUTPUT过程本身会执行成功但Amount拿到的是初始状态下的值通常是NULL而且SQL Server不会报任何错误这类bug特别隐蔽。我的排查经验是凡是“过程执行了但输出值不对”的问题第一时间检查调用侧的OUTPUT关键字有没有漏。再精确一点输出参数的变量如果是字符串或数值类型用OUTPUT时还可以在前面加OUT二者等价但全写OUTPUT更不容易产生歧义。默认值的写法前面已经出现过OrderStatus NVARCHAR(20) NULL。需要注意带默认值的参数在定义时要放在所有不带默认值参数的后面否则SQL Server会报语法错误。这也是SQL Server比某些编程语言更严格的地方——它不允许你把带默认值的参数放在前面。2.2 DECLARE变量、SET与SELECT赋值的区别在存储过程内部临时变量用DECLARE声明DECLARE CurrentDate DATETIME GETDATE(); DECLARE Count INT; DECLARE UserName NVARCHAR(50);赋值有两种方式SET Count 0;SELECT Count COUNT(*) FROM dbo.Orders WHERE UserId 1001;这两个看着差不多但有个经典区别SET只能给一个变量赋一个标量值SELECT可以从查询结果里取数赋值而且可以同时给多个变量赋值。更重要的是当SELECT查询返回多行时SQL Server会取“最后一行”的值赋给变量这个行为不报错但很容易误导人。如果你只关心唯一值尽量在SELECT赋值时确保查询能返回一行且不返回多行或者加TOP 1配合ORDER BYSELECT TOP 1 UserName UserName FROM dbo.Users WHERE UserId 1001;我见过不少线上事故就是因为这种“多行赋值取最后一行”的SQL改了索引或数据分布后最后一行变了变量值跟着变整个业务逻辑就歪了。所以能确定唯一行就用主键查询确定不了就显式加TOP 1。2.3 IF-ELSE、WHILE、CASE和GOTO存储过程内部控制流最常用的是IF...ELSEIF TotalAmount 10000 BEGIN -- 走VIP逻辑 UPDATE dbo.Users SET UserLevel VIP WHERE UserId UserId; END ELSE IF TotalAmount 5000 BEGIN -- 走普通会员逻辑 UPDATE dbo.Users SET UserLevel Member WHERE UserId UserId; END ELSE BEGIN UPDATE dbo.Users SET UserLevel Normal WHERE UserId UserId; END记住T-SQL的BEGIN...END相当于其他语言里的大括号如果IF下面只有一条语句可以省略BEGIN...END但省略后很容易在后续加语句时踩坑因为SQL Server会把第一条语句作为IF的执行体其他语句变成无条件执行。我见过一个真实的bug原来是IF Flag1 UPDATE ...后来在下面加了一行DELETE ...结果不管Flag等于几删除都会执行。从此我的规矩是只要写了IF下面一律用BEGIN...END包住。WHILE循环在存储过程里主要用于批次处理DECLARE BatchSize INT 1000; DECLARE Processed INT 1; WHILE Processed 0 BEGIN DELETE TOP (BatchSize) FROM dbo.ArchivedOrders WHERE ArchiveDate DATEADD(DAY, -30, GETDATE()) AND Status Closed; SET Processed ROWCOUNT; -- 每删一批歇100毫秒降低对生产环境的影响 WAITFOR DELAY 00:00:00.100; ENDROWCOUNT是上一个语句影响的行数每次执行后要立刻读取因为它会被下一条语句重置。这里用DELETE TOP分批删除就是为了避免一次性删除几十万行把事务日志撑爆、锁表时间过长。这个模式在数据清理归档的存储过程里非常重要。CASE在T-SQL里是表达式不是语句它返回一个值不能用来做流程控制只能用于SELECT列表、WHERE条件或赋值中SELECT OrderNo, CASE WHEN Status Paid THEN 已支付 WHEN Status Pending THEN 待支付 WHEN Status Cancelled THEN 已取消 ELSE 未知 END AS StatusText FROM dbo.Orders;注意CASE表达式里每个WHEN分支的结果类型要一致否则SQL Server会做隐式转换遇到无法转换的值就报错。2.4 表值参数和临时表的配合如果要往存储过程里传一个“列表”在低版本SQL Server里只能拼逗号分隔字符串再拆麻烦且容易出错。从SQL Server 2008开始支持了表值参数Table-Valued Parameter用法分三步第一步创建自定义表类型CREATE TYPE dbo.OrderIdList AS TABLE ( OrderId INT NOT NULL PRIMARY KEY );第二步存储过程里声明参数为这个类型CREATE OR ALTER PROCEDURE dbo.usp_BatchUpdateOrderStatus OrderIds dbo.OrderIdList READONLY, Status NVARCHAR(20) AS BEGIN SET NOCOUNT ON; UPDATE o SET o.Status Status FROM dbo.Orders AS o INNER JOIN OrderIds AS ids ON o.OrderId ids.OrderId; END表值参数必须加READONLY不允许在存储过程中对表值参数做增删改。第三步调用侧构造表值参数传入DECLARE Ids dbo.OrderIdList; INSERT INTO Ids (OrderId) VALUES (1001), (1002), (1003); EXEC dbo.usp_BatchUpdateOrderStatus OrderIds Ids, Status Shipped;表值参数最大的好处是消除了“拆字符串”的环节也避免了动态SQL拼接注入的风险传几千个ID完全没问题。超过一万个的话级别还是有点吃力那种情况我一般会先写入临时表再传表名或者把数据先批量插入正式表再用存储过程处理。3. 游标和动态SQL两个用不好就容易“翻车”的进阶语法3.1 游标什么时候真的必须用SQL是一个面向集合的语言用游标逐行处理数据在绝大多数场景下都是反模式性能比集合操作低一个量级。我在代码评审里看到DECLARE CURSOR就会条件反射地警觉。但游标并不是一无是处有些场景确实绕不开需要对每一行做一次独立的存储过程调用且调用的参数就是该行数据需要在循环里对同一行的多列做复杂的、依赖前一行结果的逐步计算需要对一张大表的每一行执行一次不一样的DML操作且无法用CASE或JOIN表达。如果你只是“想逐行处理”那大概率是没找到写集合操作的正确姿势。真正需要游标时标准写法是这样的DECLARE OrderId INT; DECLARE OrderNo NVARCHAR(50); DECLARE cur_orders CURSOR LOCAL FAST_FORWARD FOR SELECT OrderId, OrderNo FROM dbo.Orders WHERE Status Pending ORDER BY OrderDate; OPEN cur_orders; FETCH NEXT FROM cur_orders INTO OrderId, OrderNo; WHILE FETCH_STATUS 0 BEGIN -- 每行业务处理逻辑比如调用另一个存储过程 EXEC dbo.usp_ProcessSingleOrder OrderId OrderId; FETCH NEXT FROM cur_orders INTO OrderId, OrderNo; END CLOSE cur_orders; DEALLOCATE cur_orders;重点说几个细节第一LOCAL FAST_FORWARD。LOCAL表示游标只在当前批处理内有效用完自动销毁避免把游标定义成全局游标污染会话FAST_FORWARD告诉SQL Server这个游标只能向前滚动并且启用优化路径性能最好。如果只是顺序扫描不要用SCROLL或DYNAMIC那些会带来额外的锁和性能开销。第二FETCH_STATUS在每次FETCH之后会被更新值为0表示成功-1表示超出结果集末尾-2表示取到的是被删除的行。循环判断依据它。第三CLOSE和DEALLOCATE的区别CLOSE是关闭游标释放当前结果集但游标本身还在可以OPEN重新打开DEALLOCATE是彻底释放游标占用的资源。写完整的最佳实践是处理完就CLOSE如果接下来不再用就顺手DEALLOCATE。还有一个容易忽略的点游标的性能不只是“慢”还会占用锁资源。游标默认是READ_ONLY的加上FAST_FORWARD后锁粒度会比较轻。如果你需要可更新游标建议加上SCROLL_LOCKS或OPTIMISTIC中的一种不要用默认的动态游标很容易出现并发冲突。3.2 动态SQL的正确打开方式EXEC vs sp_executesql动态SQL在存储过程里很常用比如表名或列名不能作为参数直接传入或者要动态拼接筛选条件。T-SQL里执行动态SQL有两条路EXEC (SELECT * FROM dbo.Orders WHERE OrderId OrderId);EXEC sp_executesql NSELECT * FROM dbo.Orders WHERE OrderId OrderId, NOrderId INT, OrderId OrderId;最核心的差别是前者是字符串拼接后者是参数化查询。参数化除了能防止SQL注入还有一个非常关键的好处——它生成的执行计划可以被参数值不同的调用复用减少编译开销降低CPU压力。用EXEC拼接动态SQL的经典反面教材DECLARE SQL NVARCHAR(MAX); DECLARE UserId INT 1001; SET SQL SELECT * FROM dbo.Orders WHERE UserId CAST(UserId AS NVARCHAR(20)); EXEC (SQL);这个写法在真实系统里非常危险。用户的输入一旦拼进SQL黑客构造一个UserId 1 OR 11就能把整张表拖走还能用EXEC xp_cmdshell之类的方式提权。虽然SQL注入已经是老生常谈但我这两年做安全排查时依然能在内部系统里翻出这种代码。使用sp_executesql的参数化写法DECLARE SQL NVARCHAR(MAX); DECLARE UserId INT 1001; SET SQL NSELECT * FROM dbo.Orders WHERE UserId UserId; EXEC sp_executesql SQL, NUserId INT, UserId UserId;注意字符串前面的NNVARCHAR类型字符串必须加N前缀否则中文字符会乱码。这个我以前吃过亏在存储过程中拼中文的WHERE条件时忘了写N所有匹配项全部查不到排查了大半天才发现是参数声明里少了N。3.3 动态SQL里最常犯的三个错误第一个是权限问题。存储过程本身被授权执行但存储过程中的动态SQL是在调用者的安全上下文里执行的如果调用者没有访问目标表的权限动态SQL就会报错。解决方法是给存储过程开EXECUTE AS或者用证书签名但这属于高级用法一般不建议滥用宁可让DBA显式授权。第二个是字符串拼接时的引号转义。如果SQL里需要拼入字符串字面量单引号要写两次DECLARE Status NVARCHAR(20) Paid; DECLARE SQL NVARCHAR(MAX); SET SQL NSELECT * FROM dbo.Orders WHERE Status Status ; EXEC (SQL);这个写法很容易数错引号层级初学者一脸懵。所以只要能用参数传入的一定要用sp_executesql的参数列表不要拼字符串。参数化之后单引号问题就不存在了。第三个是NVARCHAR(MAX)的长度截断。动态SQL拼接时如果变量定义为NVARCHAR(4000)SQL会被截断到4000字符而导致语法错误。现在新代码我一律写NVARCHAR(MAX)省得以后SQL太长被截出莫名其妙的问题。4. 错误处理与事务生产环境的存储过程必须写TRY...CATCH4.1 使用TRY...CATCH捕获异常T-SQL的错误处理和C#、Java里类似用BEGIN TRY和BEGIN CATCH包住可能出错的逻辑CREATE OR ALTER PROCEDURE dbo.usp_CreateOrder UserId INT, ProductId INT, Quantity INT, Amount DECIMAL(18, 2) AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; DECLARE OrderId INT; BEGIN TRY BEGIN TRANSACTION; INSERT INTO dbo.Orders (UserId, ProductId, Quantity, Amount, Status, CreateDate) VALUES (UserId, ProductId, Quantity, Amount, Pending, GETDATE()); SET OrderId SCOPE_IDENTITY(); UPDATE dbo.Products SET StockQty StockQty - Quantity WHERE ProductId ProductId; COMMIT TRANSACTION; SELECT OrderId AS OrderId; END TRY BEGIN CATCH IF TRANCOUNT 0 ROLLBACK TRANSACTION; DECLARE ErrorMessage NVARCHAR(4000) ERROR_MESSAGE(); DECLARE ErrorSeverity INT ERROR_SEVERITY(); DECLARE ErrorState INT ERROR_STATE(); RAISERROR(ErrorMessage, ErrorSeverity, ErrorState); END CATCH END这个例子是很经典的“先插入主表、再扣库存”的业务场景必须保证两条语句要么都成功要么都失败。TRY...CATCH捕获错误后第一时间检查TRANCOUNT并回滚事务防止事务悬挂。ERROR_MESSAGE()、ERROR_SEVERITY()、ERROR_STATE()是CATCH块里能直接调用的系统函数。还有ERROR_NUMBER()和ERROR_LINE()ERROR_LINE()能告诉你是存储过程里第几行出错这个对排查问题非常有用。4.2 XACT_ABORT和事务回滚的细节单独说下SET XACT_ABORT ON。如果没有这句很多运行时错误比如字符串截断、除零错误、约束冲突只违反当前一条语句但不会终止整个批处理和事务。后果是你的事务只回滚了出错的那条语句之前已经INSERT/UPDATE的数据全留在库里而CATCH块里若再执行ROLLBACK就会碰到一个很迷惑的现象——“事务已经在语句级回滚了”。举个例子你在事务里先插入一条订单然后更新库存时出现字符串截断错误如果没有XACT_ABORT ONINSERT不会回滚库存更新失败事务处于“能提交但有逻辑缺失”的状态。如果后续代码判断出错再回滚事务才整个消失。很多线上“脏数据”就是这么来的。所以我的铁律是存储过程只要涉及多语句写操作开头就加SET XACT_ABORT ON配合TRY...CATCH这样才能保证“出错即整体回滚”。4.3 嵌套事务和保存点T-SQL里的BEGIN TRANSACTION是可以嵌套的但TRANCOUNT不是真正的嵌套——内层COMMIT只是让TRANCOUNT减1外层COMMIT才是真正的提交点。很多人以为嵌套事务能实现部分回滚这是典型的误区。如果确实只想回滚部分操作就要用保存点SAVE TRANSACTIONBEGIN TRY BEGIN TRANSACTION; -- 第一批操作 INSERT INTO dbo.Orders ...; SAVE TRANSACTION OrderInserted; -- 第二批操作可能出错 UPDATE dbo.Products SET StockQty StockQty - Quantity WHERE ProductId ProductId; COMMIT TRANSACTION; END TRY BEGIN CATCH IF TRANCOUNT 0 BEGIN ROLLBACK TRANSACTION OrderInserted; COMMIT TRANSACTION; END -- 记录错误日志后重新抛出 INSERT INTO dbo.ErrorLog (ErrorMessage, ErrorLine, ErrorTime) VALUES (ERROR_MESSAGE(), ERROR_LINE(), GETDATE()); END CATCH保存点允许你只回滚到某个点而不是回滚整个事务。这个技术在复杂的多阶段处理里很有用但一般业务逻辑我建议尽量简单化能用集合操作绝不上这么复杂的控制流。4.4 错误日志与RAISERROR/THROW生产环境的存储过程光有TRY...CATCH不够还需要把错误记录下来方便事后查日志。我习惯在CATCH块里同时做两件事把错误插入日志表然后RAISERROR或THROW把错误重新抛给应用层。RAISERROR是老语法THROW是SQL Server 2012引入的新语法THROW 50001, 订单创建失败, 1;THROW更简洁还可以不带参数直接写在CATCH里表示原样重新抛出原始错误BEGIN CATCH INSERT INTO dbo.ErrorLog (...) VALUES (...); THROW; END CATCH需要注意THROW无法捕获时终止会话中的批处理所以调用方的TRY...CATCH会收到异常。我们经常在存储过程的CATCH块里写THROW来保留最原始的错误信息而不用重新拼一个错误文本。4.5 错误处理的常见坑第一个坑是CATCH块里执行了会引发新错误的语句导致原始错误信息丢失。比如CATCH里再写一条不合法的INSERT整个会话会抛出新错误原始的被覆盖。处理方式是把错误日志插入操作也包一层TRY...CATCH或者用SAVE TRANSACTION包裹。第二个坑是RAISERROR的严重级别。用11~16会返回普通异常19以上默认是系统级别错误会导致客户端连接断开。一般不要用20以上的级别。第三个坑是事务和CATCH的位置。如果事务在TRY外面开始CATCH块里的ROLLBACK会失败因为CATCH上下文里看不到最外层事务的语句这种情况SQL Server会抛“当前事务无法提交”的错误。5. 不同SQL Server版本下的语法差异与兼容性5.1 版本演进对存储过程语法的影响很多公司还在用SQL Server 2008 R2、2012、2014而且这些老版本的生命周期早已停止更新只是业务还在跑。如果你写的存储过程要兼容多个版本语法选择就必须谨慎。这里梳理几个我踩过版本坑的语法点5.2 老版本缺失的实用语法DROP IF EXISTSSQL Server 2016才支持。2008-2014里只能这么写IF OBJECT_ID(dbo.usp_GetUserOrders, P) IS NOT NULL DROP PROCEDURE dbo.usp_GetUserOrders;STRING_AGGSQL Server 2017才支持。老版本要把多行拼成逗号字符串只能靠FOR XML PATHSELECT STUFF(( SELECT , UserName FROM dbo.Users WHERE DepartmentId DeptId ORDER BY UserName FOR XML PATH() ), 1, 1, );这个写法是现代SQL Server里90%字符串聚合需求的解法虽然看着绕但在2012/2014上它就是标准答案。注意变量别用错类型要声明为NVARCHAR(MAX)防止截断。OFFSET-FETCH分页SQL Server 2012才支持。2008里只能用ROW_NUMBER()配合BETWEEN实现;WITH Paged AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY OrderId DESC) AS RowNum FROM dbo.Orders ) SELECT * FROM Paged WHERE RowNum BETWEEN 1 AND 20;2012直接用SELECT * FROM dbo.Orders ORDER BY OrderId DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;注意OFFSET-FETCH必须要有ORDER BY没有ORDER BY会直接报错。IIF、CONCAT2012引入。2016还引入了DROP IF EXISTS、STRING_SPLIT2017引入了TRIM这些都是写新存储过程时很顺手但老库用不了的功能。如果你的数据库版本在2016以下用CONCAT时要注意它不接受NULL会返回空字符串和拼接时NULL照样是NULL的行为完全不同。JSON函数SQL Server 2016开始支持OPENJSON、JSON_VALUE。如果要在2008/2012里处理JSON就只能用CLR或动态SQL非常痛苦。5.3 兼容级别的坑数据库的兼容级别compatibility level决定了它支持哪些语法。比如数据库兼容级别是100对应2008即使你装在SQL Server 2019的实例上用STRING_AGG还是会报“不是可识别的内置函数名称”。这个坑特别容易忽略因为SSMS连上后看起来是2019但SELECT compatibility_level FROM sys.databases WHERE name 你的库查出来可能是100或110。如果需要用新函数可以改兼容级别ALTER DATABASE [YourDB] SET COMPATIBILITY_LEVEL 150; -- 对应SQL Server 2019这个操作要评估影响面改兼容级别可能改变部分查询优化器行为不是顺手就能改的。在多个版本共存的开发环境里我一般会让开发库和生产库的兼容级别保持严格一致避免“开发能跑生产报错”。5.4 SQL Server 2022里新增的几个实用函数如果你的项目已经用上SQL Server 2022几个新函数对存储过程写法帮助很大DATETRUNC(datepart, date)按指定精度截断时间比如DATETRUNC(month, GETDATE())取当月第一天比之前的DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)好懂太多。GREATEST和LEAST在一组值里取最大/最小省去嵌套CASE WHEN。STRING_SPLIT增加了ordinal参数可以返回分割后每个元素的下标老版本里只能顺序无序号。IS [NOT] DISTINCT FROM处理NULL的比较NULL IS DISTINCT FROM 1返回true这让WHERE条件里写OrderStatus IS NOT DISTINCT FROM o.Status很方便比(OrderStatus IS NULL OR o.Status OrderStatus)简洁。但是注意用这些新函数前先确认目标环境的SQL Server版本和兼容级别否则一到部署环节就暴露兼容问题轻则DBA驳回脚本重则生产发布失败。6. 存储过程调优与常见坑基于实际排障的经验总结6.1 参数嗅探问题为什么同样的过程时快时慢存储过程第一次执行时SQL Server会根据传入的参数值生成执行计划并缓存在计划缓存里后续调用默认复用这个计划。问题是如果第一次传了一个数据分布极端的参数值比如查一个占总数据量1%的ID优化器会生成一个针对“少量数据”的查询计划后面再传一个占总数据量50%的ID时SQL Server还是复用那个“少量数据”的计划就可能出现全表扫描或者严重的行估计差异表现为同一个存储过程“有时秒回有时死慢”。这是存储过程性能问题里最经典的“参数嗅探”。常用解法有这几种加OPTION (RECOMPILE)每次执行都重新生成计划适合执行频率低但单次操作重的过程代价是每次都有编译开销。加OPTION (OPTIMIZE FOR (参数 某典型值))让优化器按指定的典型参数值生成计划。适合参数分布稳定、可以找到“中庸”值的场景。在局部变量里复制参数再用于查询屏蔽原始参数值的影响。使用WITH RECOMPILE执行即调用侧临时重编译。实际排障时我会先看执行计划里Estimated Number of Rows和Actual Number of Rows差多少差出两个数量级以上基本就是参数嗅探或统计信息过期。6.2 临时表vs表变量选择不对性能差十倍存储过程里经常需要中间结果集。临时表#temp和表变量table都常用但特性差异很大对比项临时表 #temp表变量 table统计信息有自动更新无只有表定义时的大小估计索引可创建主键和索引可创建主键和约束索引能力较弱作用域当前会话可被子过程访问当前批次不能被子过程访问事务回滚参与事务可回滚不参与事务回滚不影响表变量中的数据适用场景数据量大、需要JOIN复杂查询数据量小、传参、临时缓存我自己的经验法则是数据行数超过几百行且后续要多表JOIN用#临时表只有几行或纯粹当列表传参用表变量。在优化慢存储过程时把表变量换成临时表往往执行时间直接降一个数量级因为优化器对临时表有统计信息能估算准确行数。6.3 隐式转换导致的索引失效存储过程中最常见的坑之一是参数类型和列类型不一致导致的隐式转换。比如列OrderNo是NVARCHAR(50)你在WHERE里写OrderNo 100123整数SQL Server会把列类型转换成整数导致该列上的索引无法正确利用。-- 错误OrderNo是NVARCHAR参数却是INT WHERE OrderNo OrderIdINT-- 正确参数类型与列类型一致 WHERE OrderNo OrderNoNVARCHAR排查方法很简单看执行计划里有没有CONVERT_IMPLICIT这个运算符只要出现它就说明有隐式转换在阻碍索引利用。生产环境里很多慢查询其实就是隐式转换造成的把参数类型改成和列一致问题瞬间消失。6.4 不要在存储过程里用标量函数存储过程里调用自定义标量函数Scalar Function往往会导致查询无法并行、每行都要调用一次在大数据量下性能极差。尤其是嵌套在SELECT列表或WHERE条件里时代价更大。比如这个写法每查一行就要把dbo.ufn_GetUserLevel(UserId)跑一遍SELECT UserName, dbo.ufn_GetUserLevel(UserId) AS UserLevel FROM dbo.Users;同样的逻辑能用CASE、LEFT JOIN或内联表值函数就不要用标量函数。SQL Server 2019之后的标量函数内联化Scalar UDF Inlining特性可以在一定条件下自动优化但依赖版本和函数复杂度不可控最好还是自己在设计阶段就避开。6.5 NOLOCK到底能不能用WITH (NOLOCK)在存储过程里经常出现很多人把它当成性能优化神器。它实际上使用的是“脏读”语义——可以读取到其他事务未提交的数据而且可能读到重复行或跳过行。对于报表类、对一致性要求不高的查询场景NOLOCK确实能减少阻塞但对于订单、库存这类强一致性业务用了NOLOCK出现“读到未提交的库存扣减”这种问题后果很严重。我的建议是在线交易类存储过程坚决不用NOLOCK偏查询统计的过程如果阻塞严重优先考虑开启READ COMMITTED SNAPSHOT隔离级别而不是简单粗暴地加NOLOCK。6.6 哪些场景不应该用存储过程最后说个反常识的体会不是所有逻辑都适合塞进存储过程。用了存储过程代码版本控制、单元测试、跨数据库迁移都会增加额外的成本。如果只是简单CRUD用ORM直接操作还更直观。如果要做非常复杂的报表查询有时候用视图或直接写查询SQL都比存储过程灵活。我这些年维护老系统最大的感受是存储过程适合做“接口稳定、逻辑相对固定、需要进行权限控制或减少网络往返”的场景而频繁变化的业务规则、需要大规模重构的逻辑勉强用存储过程实现只会让你维护得想离职。所以把存储过程当工具而不是信仰才是正确的态度。最后再分享一个我在实际项目里养成的习惯写完存储过程不要直接结束养成习惯执行一下sp_helptext或者直接查sys.sql_modules把过程定义导出到版本控制里和代码一起走评审和发布。很多团队数据库脚本没有纳入Git管理生产环境的过程和开发环境不一致出了事故DBA改完又没人知道后果就是下次上线直接覆盖生产修复。把存储过程脚本当成一等公民纳入版本管理、做自动化部署虽然前期麻烦点但长期来看能省掉无数个“生产为什么和开发不一样”的抓狂夜晚。