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

结果或目标

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

准备条件

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

真实操作顺序

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

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

操作：

1. 声明必要变量：包括表名字符串变量(@TableName)、SQL语句变量(@SqlString)及过滤参数(@CourseID)。
2. 赋值变量：将表名和参数值分别赋给对应变量。
3. 构造SQL：将表名用 QUOTENAME() 函数包裹以防止注入风险，并与参数值拼接成完整的SQL字符串。注意字符串必须为 NVARCHAR 类型以支持Unicode。
4. 执行语句：调用 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)来传递参数，避免字符串拼接带来的安全隐患和性能损耗。

操作：

1. 编写带占位符的动态SQL字符串，例如使用 @orderid 作为筛选条件。
2. 定义参数规范字符串，指明占位符的数据类型（如 N'@orderid AS INT'）。
3. 在调用时，将具体的变量值传递给 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步：实现带输入/输出参数的复杂查询

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

操作：

1. 创建存储过程 sp_GetNameByUserId，声明输入参数 @userId 和输出参数 @userName。
2. 在过程中定义动态SQL，利用 @userName 作为输出变量接收查询结果。
3. 使用 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场景，常用于复杂的报表或日志统计。

操作：

1. 定义输入表名 @tableName、两个过滤条件 @proid 和 @id，以及一个用来接收计数结果的 @count。
2. 构造SQL语句：N'select @countOUT=count(empid) from ' + @tableName + N' where proid=@proid1 and id<@id1'。
3. 声明参数映射：使用 @ParmDefinition 定义所有涉及的参数及其方向。
4. 执行并验证：通过 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 方式，适用于简单的字符串拼接场景，但需注意安全性较低的问题。

操作：

1. 打印测试：直接使用单引号转义双引号的写法，在动态内容中嵌入普通文本。
2. 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);-- 执行拼接后的完整字符串

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

简要总结

SQL Server提供了两套机制来处理动态构建的查询逻辑。虽然 EXEC 命令实现简单，适合无参或少参的脚本执行；但在实际业务特别是涉及用户输入的搜索和分页场景中，强烈建议使用 sp_executesql。

sp_executesql 的核心优势在于其参数化能力：
1. **安全性**：将逻辑与数据结构分离，从根本上防止SQL注入攻击。
2. **性能优化**：由于参数化特征，数据库更容易重用已缓存的执行计划，减少编译开销。
3. **维护性**：代码更易读，且天然支持多种数据类型的无缝传递。

以上关于 SQL Server 中执行动态SQL的详细内容，更多关于 T-SQL高级应用及数据库安全优化的资料请关注本站其它相关文章！