SQL Server中执行动态SQL：sp_executesql与EXEC对比及实例

陆遥

发布时间：2026-10-15 | 5人浏览过

原创

在SQL Server开发中，经常需要根据外部输入动态构建查询逻辑，例如在通用分页存储过程中传入不同的表名、过滤条件和排序字段，或在搜索功能中根据用户选择灵活调整WHERE子句。本文将详细讲解两种执行动态SQL的方式，并通过实际代码演示如何安全高效地实现这一需求。

结果或目标

掌握使用 sp_executesql 和 EXEC 两种系统在SQL Server中执行动态生成的SQL字符串的方法，理解参数化处理对性能和安全性的提升，并能够编写支持变量表名、参数化筛选条件的存储过程或查询脚本。

准备条件

已安装并配置好Microsoft SQL Server环境（兼容SQL Server 2005及以上版本）。
具备基础的T-SQL编程知识，了解变量声明、赋值及存储过程的基本语法。
准备好用于测试的数据表（如课程表、订单表或学生信息表）。

真实操作顺序

第1步：使用 sp_executesql 执行简单动态查询

目的：演示如何利用sp_executesql安全地拼接包含变量表名的查询语句，并展示基础执行流程。

操作：

声明必要变量：包括表名字符串变量(@TableName)、SQL语句变量(@SqlString)及过滤参数(@CourseID)。
赋值变量：将表名和参数值分别赋给对应变量。
构造SQL：将表名用 QUOTENAME() 函数包裹以防止注入风险，并与参数值拼接成完整的SQL字符串。注意字符串必须为 NVARCHAR 类型以支持Unicode。
执行语句：调用 sys.sp_executesql 执行构造好的SQL。

declare @TableName varchar(50), @SqlString nvarchar(200), @CourseID int;
set @TableName = '课程表';
set @CourseID = 1;
-- 使用 QUOTENAME() 确保表名安全，N前缀表明为Unicode字符串
set @SqlString = N'select * from ' + quotename(@TableName) + N'where ID = ' + cast(@CourseID as varchar(10));
exec sp_executesql @SqlString;

完成状态：系统返回指定课程ID的记录集。

第2步：使用输入参数简化动态SQL

目的：学习如何通过 sp_executesql 的参数定义列表(@params)来传递参数，避免字符串拼接带来的安全隐患和性能损耗。

操作：

编写带占位符的动态SQL字符串，例如使用 @orderid 作为筛选条件。
定义参数规范字符串，指明占位符的数据类型（如 N'@orderid AS INT'）。
在调用时，将具体的变量值传递给 sp_executesql。

declare @sql as nvarchar(100);
-- 构建带参数的SQL片段
set @sql = N'SELECT orderid,custid,empid,orderdate FROM Sales.Orders WHERE orderid=@orderid;'
-- 传入参数值进行执行
exec sys.sp_executesql @stmt = @sql, @params = N'@orderid AS INT', @orderid = 10248;

完成状态：仅返回符合指定 OrderID 的订单详情，且参数由数据库引擎自动处理类型，无需手动转换字符串。

第3步：实现带输入/输出参数的复杂查询

目的：创建一个完整的存储过程示例，结合输入参数筛选数据，并通过输出参数将统计结果带回。

操作：

创建存储过程 sp_GetNameByUserId，声明输入参数 @userId 和输出参数 @userName。
在过程中定义动态SQL，利用 @userName 作为输出变量接收查询结果。
使用 sp_executesql 执行该SQL，并在 @params 中明确标记输出参数（output），同时在调用时将过程变量标记为 output 以接收值。

create procedure sp_GetNameByUserId(@userId varchar(100), @userName varchar(100) output)
as
 declare @sql nvarchar(1000);
 set @sql=N'select @userName=UserName from Student where UserId=@userId';
 -- 定义参数结构，注意标明 input/output
 exec sp_executesql @sql,N'@userId varchar(100),@userName varchar(100) output', @userId, @userName output;
select @userName;-- 验证输出结果

完成状态：调用存储过程后，可在控制台看到获取到的用户姓名。

第4步：综合实例 - 多参数条件计数

目的：展示一个包含多个输入参数和一个输出参数的综合性动态SQL场景，常用于复杂的报表或日志统计。

操作：

定义输入表名 @tableName、两个过滤条件 @proid 和 @id，以及一个用来接收计数结果的 @count。
构造SQL语句：N'select @countOUT=count(empid) from ' + @tableName + N' where proid=@proid1 and id<@id1'。
声明参数映射：使用 @ParmDefinition 定义所有涉及的参数及其方向。
执行并验证：通过 sp_executesql 将实际变量值绑定到SQL中的占位符。

declare @count          int,
        @tableName      nvarchar(50),
        @SQLString      nvarchar(max),
        @proid          int,
        @id             int,
        @ParmDefinition nvarchar(max);

set @tableName=N'mytable';
set @proid=433;
set @id=159;

-- 注意：动态SQL内部也要定义输出参数名（如 @countOUT）
set @SQLString=N'select @countOUT=count(empid) from '
               + @tableName
               + N' where proid=@proid1 and id<@id1';

-- 定义参数清单，指定每个变量的数据类型及是否为 output
set @ParmDefinition=N'@proid1 int,@id1 int,@countOUT   int   output';

-- 执行时绑定实际变量，并将接收结果的变量标记为 output
exec sp_executesql
  @SQLString,
  @ParmDefinition,
  @proid1=@proid,
  @id1=@id,
  @countOUT=@count output;

select @count; -- 打印最终的计数结果

完成状态：屏幕显示符合条件的记录总数。

第5步：使用 EXEC 命令执行基础动态SQL

目的：了解传统的 EXEC 方式，适用于简单的字符串拼接场景，但需注意安全性较低的问题。

操作：

打印测试：直接使用单引号转义双引号的写法，在动态内容中嵌入普通文本。
IN列表拼接：将逗号分隔的ID字符串直接拼接到SQL末尾的 IN (...) 子句中。

-- 示例1：简单打印
DECLARE @sql AS NVARCHAR(100);
SET @sql = N'PRINT ''这条消息是动态SQL命令打印的.'';';
EXEC (@sql);

-- 示例2：拼接IN列表（不推荐用于用户输入，易导致SQL注入）
DECLARE @sql AS NVARCHAR(100);
DECLARE @OrderIDs AS NVARCHAR(50) = N'10248,10249,10250';
SET @sql = N'SELECT * FROM Sales.Orders WHERE orderid IN (' + @OrderIDs + N');';
EXEC (@sql);-- 执行拼接后的完整字符串

完成状态：成功触发打印操作并拉取相关订单数据。