SQL Server存储过程与函数：区别、实战与性能优化

做SQL Server开发的人，迟早都要和存储过程（PROCEDURE）还有函数（FUNCTION）打交道。这两个对象几乎天天见，但很多人对它们的理解停留在"能用就行"的层面，真要回答"什么场景必须用存储过程、什么场景只能写函数、为什么函数不能随便嵌套存储过程"这些问题，往往说不出个所以然。这篇文章我不打算抄官方文档，就把这些年实际写存储过程和函数踩过的坑、总结的规矩，按照"先分清楚区别，再逐个上手，最后聊性能和安全"的顺序整理出来。适合刚接触数据库开发的新手当入门手册，也适合已经写了一段时间但想理清思路的开发者对照自查。

1. 先分清楚：存储过程和函数到底差在哪里

1.1 两者的定位完全不同

很多初学者把存储过程和函数当成同一类东西，觉得都是"把一段SQL封装起来，需要的时候调用一下"。这个理解不能说错，但会埋下很多坑。存储过程的核心定位是"完成一组操作"，它强调的是过程，可能是一条查询，也可能是连续的多条增删改，再配合事务、错误处理、动态SQL，它的能力边界远远超过函数。函数的核心定位是"计算并返回一个值"，它强调的是输入输出之间的确定性关系，函数本身不应该修改数据库以外的状态，所以在函数体里写INSERT、UPDATE、DELETE会受到严格限制。

一个很直观的对比是调用方式。存储过程用EXEC调用，比如EXEC dbo.usp_GetUserInfo @UserId = 123；函数则是像普通内置函数一样嵌在SQL表达式里，比如SELECT dbo.fn_GetUserName(UserId) FROM dbo.Users。这意味着函数能出现在SELECT列表、WHERE条件、CHECK约束、计算列里，而存储过程不行。反过来，存储过程可以返回多个结果集，函数只能返回一个确定的值或一张表。这个差异直接决定了它们的使用边界，很多时候选错类型不是因为不会写，而是没想清楚调用方到底需要什么形态的结果。

1.2 选型：什么时候用哪个

我一般遵循几条很朴素的判断标准。如果这段逻辑要在一个查询里对多行数据逐个计算，那必须用函数，因为只有函数能嵌进SELECT表达式；如果这组操作要修改多张表的数据、需要事务保护、需要抛出业务错误，那就应该用存储过程，因为函数没有完整的错误处理能力，也不能在事务失败时灵活回滚到指定位置。还有一条经验：如果同一个逻辑既想给查询用，又想给业务调度用，通常拆成"一个函数负责纯计算 + 一个存储过程负责组装操作"，各干各的活，不要硬塞在一起。

提示：不要把只需要一次查询的简单逻辑硬包成存储过程。封装本身有维护成本，SQL Server每次执行存储过程还要走一次编译和缓存的判断，过度封装在简单场景里反而是负优化。先问自己"这段代码会被重复调用吗""调用方需要什么样的返回形式"，答案明确了再动手。

2. 存储过程实战：从创建到调优

2.1 创建存储过程的基本姿势

一个规范的存储过程，骨架通常长这样：

CREATE PROCEDURE dbo.usp_GetOrdersByStatus @Status TINYINT, @PageSize INT = 20, @PageIndex INT = 1 AS BEGIN SET NOCOUNT ON; SELECT OrderId, CustomerName, Status, CreateTime FROM dbo.Orders WHERE Status = @Status ORDER BY CreateTime DESC OFFSET (@PageIndex - 1) * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY; END

这里有几个细节值得注意。第一，schema前缀一定要写，dbo.usp_...这种写法避免SQL Server先去猜你在找哪个schema的对象，省掉一次元数据解析，更重要的是避免同名对象在不同schema下带来的混乱。第二，命名规范建议统一，存储过程用usp_前缀，函数用fn_前缀，触发器用trg_前缀，看名字就知道类型，排查问题的时候能省很多时间。第三，SET NOCOUNT ON一定要加上，它关掉受影响行数的消息返回，否则每次增删改都会往客户端回传一条"DONE_IN_PROC"消息，在循环调用存储过程时会产生大量无意义的网络流量，明细拉取接口尤其明显。

参数设计上，输入参数要明确类型和长度，能用TINYINT就不要用INT，能用INT就不要用BIGINT。我见过不少项目把所有参数都定义成VARCHAR(MAX)或者NVARCHAR(MAX)，理由是"防止超长"，结果就是索引失效、隐式转换、阻塞一大堆，排查起来非常痛苦。默认值要按业务场景来，分页参数给默认值、状态字段不轻易给默认值，这类约定最好写进团队的数据库开发规范里。

2.2 参数、返回值和错误处理

存储过程的参数分输入参数和输出参数。输出参数用OUTPUT关键字声明，适合返回单个值或者几个值，比如返回一个自增ID、一个总数。注意输出参数和RETURN的区别：RETURN只能返回整数，主要用来表示执行状态，0代表成功，非0代表失败或错误码；输出参数可以返回任意类型，返回业务数据。很多人误以为"RETURN加上结果集就能给应用层用"，实际上应用层拿结果集正常靠的是SELECT出来的数据集，RETURN在大多数驱动里只是额外暴露一个返回值，两者不要混用。

错误处理是存储过程的必修课，我写的每一个会做增删改的存储过程，都强制带TRY...CATCH块：

CREATE PROCEDURE dbo.usp_UpdateOrderStatus @OrderId INT, @NewStatus TINYINT AS BEGIN SET NOCOUNT ON; BEGIN TRY BEGIN TRAN; UPDATE dbo.Orders SET Status = @NewStatus, UpdateTime = GETDATE() WHERE OrderId = @OrderId; IF @@ROWCOUNT = 0 BEGIN THROW 51000, N'订单不存在或状态未变更', 1; END COMMIT TRAN; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRAN; THROW; -- 原样抛出，保留错误上下文 END CATCH END

注意几个容易被忽略的地方。THROW语句从SQL Server 2012开始可用，它能抛出自定义错误并带着错误号、严重级别、状态，比老式的RAISERROR更简洁，而且THROW不带参数时是原样重抛，保留最开始的错误上下文，配合ERROR_MESSAGE()、ERROR_LINE()可以在CATCH块里记录日志。@@ROWCOUNT在UPDATE后要立刻取，因为任何中间赋值语句都可能把它冲掉。事务里最好的实践是"能短则短"，不要在事务里做无关的查询，更不要调用可能执行很久的外部过程，锁资源是要钱的。

2.3 事务与并发的基本功

写事务的时候，判断当前事务状态是个细节活。TRY块里如果已经BEGIN TRAN，CATCH里需要判断XACT_STATE()，这个函数返回0表示没有活动事务，1表示事务可提交，-1表示事务处于不可提交状态，必须回滚。用@@TRANCOUNT判断逻辑会漏掉"事务是否还能提交"这个关键信息，所以在CATCH里我习惯这样写：

BEGIN CATCH IF XACT_STATE() <> 0 ROLLBACK TRAN; THROW; END CATCH

再提一个并发场景下常见的坑：先查再改的"查改间隙"。比如判断"如果订单状态是可处理，就更新为已处理"，两条语句之间可能有别的会话已经改了状态。这里要么在UPDATE的WHERE里带上原状态条件，靠@@ROWCOUNT判断是否更新成功，要么用乐观锁版本号字段，要么在事务里对关键行加更新锁。我见过线上因为这种"查改间隙"出现重复发放优惠券的问题，最后的修复方案就是改成"带条件UPDATE + 影响行数校验"，比加锁的并发开销小得多。

3. 函数实战：标量函数和表值函数

3.1 标量函数：方便但容易踩性能坑

标量函数就是输入一个或多个参数、返回一个标量值的函数。一个最简单的例子：

CREATE FUNCTION dbo.fn_GetAge ( @BirthDate DATE ) RETURNS INT AS BEGIN RETURN DATEDIFF(YEAR, @BirthDate, GETDATE()); END

写起来确实方便，SELECT dbo.fn_GetAge(BirthDate) FROM dbo.Users一行就搞定了。但这里有一个必须知道的性能陷阱：标量函数用在WHERE条件或者JOIN的关联条件上时，SQL Server会对每一行都调用一次函数，无法预计算，也无法有效利用索引，这就是典型的"行级函数调用"问题。数据量小的时候感觉不到，一旦上了百万行，查询效率会断崖式下降。我接手过一个慢查询，去掉一个标量函数之后，执行时间从十几秒降到了几百毫秒，这就是函数调用和直接表达式计算的差距。

那标量函数到底怎么用？按我的经验，适合用在计算列上、CHECK约束里、数据量可控的报表查询里，以及那些"逻辑稳定、短期不会改"的规则计算。如果函数要参与大数据量的查询过滤，优先把计算逻辑改写成JOIN或者直接写在SELECT里，让优化器能看到完整的表达式。SQL Server 2019引入了一个新的内联标量函数能力，满足条件的标量函数会被自动内联展开，性能大幅改善，但这依赖函数本身是确定性的、没有数据访问的、没有复杂控制流的，所以最稳妥的思路依然是"别在关键查询路径上依赖标量函数"。

3.2 表值函数：内联与多语句的取舍

表值函数返回的是一个表，可以像普通表一样去JOIN、去WHERE。它分两种，区别很大。

内联表值函数（Inline TVF）写法简单，没有BEGIN...END块，就是一条SELECT，返回的是TABLE类型，本质上像一个"带参数视图"：

CREATE FUNCTION dbo.fn_GetUserOrders ( @UserId INT ) RETURNS TABLE AS RETURN ( SELECT OrderId, Amount, Status, CreateTime FROM dbo.Orders WHERE UserId = @UserId );

这种函数性能最好，因为优化器可以看到函数内部的实际查询计划，能够把它和外部查询合并优化，甚至利用参数来生成高效的执行计划。

多语句表值函数（Multi-statement TVF）有一个显式的返回表结构定义，函数体可以写多个语句往这个表变量里插入数据：

CREATE FUNCTION dbo.fn_GetMonthlyReport ( @Year INT ) RETURNS @Report TABLE ( MonthNo INT, OrderCount INT, TotalAmount DECIMAL(18,2) ) AS BEGIN INSERT INTO @Report (MonthNo, OrderCount, TotalAmount) SELECT MONTH(CreateTime), COUNT(1), SUM(Amount) FROM dbo.Orders WHERE YEAR(CreateTime) = @Year GROUP BY MONTH(CreateTime); RETURN; END

这种写法灵活，可以分步处理、拼接复杂逻辑，但注意它背后其实是一个表变量，表变量没有统计信息，优化器默认假设它只有1行数据，外部JOIN时可能产生错误的执行计划。能用内联表值函数解决的问题，不建议用多语句版本；多语句版本适合那些"必须先加工、再输出"的流程化逻辑，并且使用方通常是少量数据抽取的场景。

3.3 函数使用限制与确定性规则

写函数时还有一堆"不能做"的原则。函数体里不能修改数据库状态，不能执行动态SQL，不能调用存储过程，不能使用临时表，不能使用事务控制语句。这些限制不是巧合，而是函数定位的直接体现——函数要能被优化器自由地嵌入到各种查询表达式里，自然就不能带"副作用"。如果你发现一个逻辑在函数里写不下去，那大概率这个逻辑本身就不该做成函数。

确定性也是一个重要概念。确定性函数是指"输入相同、返回结果一定相同"的函数，比如ABS、UPPER；非确定性函数比如GETDATE()、NEWID()。绑定到计算列、索引视图上的函数必须是确定性的，否则存储结果没有意义。自定义函数只有在不引用非确定性函数、不访问外部数据的情况下才算确定性，写的时候留意一下OBJECTPROPERTY里的IsDeterministic属性，真要用到索引视图或计算列索引时能少踩不少坑。

4. 常见问题排查与性能优化实录

4.1 参数嗅探：最隐蔽的性能杀手

参数嗅探（Parameter Sniffing）是我在存储过程性能问题上遇到频率最高的问题。它的机制是：存储过程第一次执行时，SQL Server会根据当时传入的参数值生成执行计划并缓存，后续执行就直接复用这个计划。听起来没问题，但参数值的数据分布差异很大时，问题就来了。比如一个筛选"已删除=1"的查询，第一次执行时存量只有几百条，优化器选择了全表扫描的计划；后面数据涨到几百万条，再传同样的参数，计划还是全表扫描，性能瞬间崩掉。

解决方案有几个层次。最常用的是在存储过程内部把参数赋给局部变量，让优化器按局部变量的统计情况生成计划，但这个方法会固定用"标准计划"，对某些场景反而会损失性能。更稳妥的是针对不同数据分布建复合索引，让一个计划能覆盖大部分情况。还有一种思路是使用OPTION(RECOMPILE)，在每次执行时重新生成计划，适合"参数数据分布极不均匀、查询频率不高、但对单次性能要求很高"的场景，比如报表的核心查询。具体用哪种，要基于对业务数据分布的了解，不能一招通吃。

4.2 函数在查询中的性能陷阱明细

除了标量函数的逐行调用问题，函数还有一个容易被忽视的坑：内联表值函数虽然优秀，但连接条件里的隐式转换会把它拉下水。比如函数的参数是INT，外部传的是VARCHAR，SQL Server就会在JOIN时做隐式转换，函数参数上的索引直接失效。排查这类问题时，最直接的手段是把执行计划打开，看有没有红色的"Table Scan"和黄色的"CONVERT"警告。

另一个常见问题是多语句表值函数的统计信息缺失。前面提到表变量默认估计1行，如果实际返回几万行，嵌套循环连接会把性能拖垮。我处理过一个案例，一个多语句表值函数被外层查询JOIN了三次，每次执行都要对函数结果做全量扫描，后面改成临时表物化中间结果，再把临时表提供给外层查询，性能提升明显。所以经验法则是：中间结果要参与大查询时，优先用临时表 + 存储过程，而不是多语句表值函数。

4.3 权限、加密与安全红线

存储过程和函数在权限模型上有一个金矿般的特性：我可以只授予用户执行存储过程的权限，而不授予底层表的权限。这在做应用系统权限收敛时特别有用，业务层只需要EXECUTE权限，底层表的SELECT/INSERT/UPDATE/DELETE全部收掉，数据访问绕不开存储过程这道关口。函数同理，但要注意函数如果暴露在查询里，调用方相当于拿到了对表数据的只读访问能力，所以要严格控制函数的SELECT权限。

还有一个经常被翻出来的洞：动态SQL拼接。存储过程里如果这样写：

EXEC('SELECT * FROM dbo.Orders WHERE Status = ''' + @Status + '''');

@Status里一旦拼入恶意片段，就会造成SQL注入。正确的姿势是用sp_executesql或者sp_executesql配参数化查询：

EXEC sp_executesql N'SELECT * FROM dbo.Orders WHERE Status = @Status', N'@Status TINYINT', @Status = @Status;

参数化之后，输入内容只是参数值，永远不会被当作SQL代码解析。记住一条底线：动态SQL没有参数化，跟裸奔没有区别。还有，存储过程可以使用WITH ENCRYPTION对定义文本加密，但它只是混淆而不是真正安全，生产环境里我更建议把定义脚本纳入版本管理，加密反而会造成代码无法维护。

4.4 常见问题速查表

这张表是我在团队里做代码评审时常用的检查清单，几乎每次都能扫出一两条问题。

5. 实操心得与避坑经验

写到最后，分享几条我个人的实操体会。第一，用版本管理维护存储过程和函数的定义脚本，不要只在SSMS里改。数据库对象也是代码，没有版本记录的数据库改动，出了问题就只能对着生产环境猜。我现在的习惯是每个对象单独一个 .sql 文件，提交记录写清楚变更原因，回滚时直接执行上一个版本的脚本。

第二，存储过程不是越短越好，也不是越长越坏，关键是单一职责。我见过上千行的超长存储过程，改一个字段要翻半天；也见过一个业务接口拆成七八个存储过程互相调用的，调试链路绕得人头大。我的标准是：一个存储过程对应一个明确的业务动作，比如"创建订单""取消订单""查询订单列表"，涉及多步骤的用事务串起来，但每一步的逻辑尽量拆成独立对象，这样单测和排查都简单。

第三，函数和存储过程的命名要带用途语义，不要用fn_1、usp_test这种名字。数据库对象名会在代码里反复出现，名字起得清楚，半年后回来看代码能省大量理解成本。另外，所有对象定义里都要写上作者和变更说明的注释，这不是形式主义，是救命的东西。

第四，上线前的检查一定要看执行计划。不要只看查询结果对不对，打开"包含实际执行计划"跑一遍核心场景，重点观察有没有表扫描、有没有隐式转换、有没有估计行数和实际行数差异巨大的运算符。很多性能问题在开发环境数据量小的时候完全看不出来，上线后数据量一涨就爆，提前看执行计划是最低成本的预防手段。

最后再提一个容易被忽略的细节：函数和存储过程的权限是跟着schema走的，要给db_datareader、db_datawriter这类固定角色之外的精确权限时，多花五分钟按对象授权，别图省事一把梭。数据库开发是个长期维护的活，今天省的时间，后面都会连本带利还回去。