sp_executesql 是 SQL Server 中一个非常重要的系统存储过程，主要用于执行动态构建的 SQL 语句。以下是它的主要作用和优势：
主要作用
1. 执行动态 SQL
sql
DECLARE @SQL NVARCHAR(MAX) DECLARE @TableName NVARCHAR(50) = 'Employees' DECLARE @Department NVARCHAR(50) = 'IT' SET @SQL = N'SELECT * FROM ' + @TableName + ' WHERE Department = @Dept' EXEC sp_executesql @SQL, N'@Dept NVARCHAR(50)', @Dept = @Department
2. 参数化查询
sql
DECLARE @SQL NVARCHAR(MAX) DECLARE @City NVARCHAR(50) = 'London' SET @SQL = N'SELECT * FROM Customers WHERE City = @CityParam' -- 使用参数化查询，避免 SQL 注入 EXEC sp_executesql @SQL, N'@CityParam NVARCHAR(50)', @CityParam = @City
主要优势
1. 防止 SQL 注入
sql
-- 不安全的写法（容易受 SQL 注入攻击） DECLARE @UserInput NVARCHAR(50) = 'London; DROP TABLE Customers--' DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM Customers WHERE City = ''' + @UserInput + '''' -- 安全的写法（使用参数化） DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM Customers WHERE City = @City' EXEC sp_executesql @SQL, N'@City NVARCHAR(50)', @City = @UserInput
2. 执行计划重用
sql
-- 多次执行相同结构的查询，可以重用执行计划 DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM Products WHERE CategoryID = @CatID' -- 第一次执行 EXEC sp_executesql @SQL, N'@CatID INT', @CatID = 1 -- 第二次执行 - 可以重用执行计划 EXEC sp_executesql @SQL, N'@CatID INT', @CatID = 2
3. 输出参数支持
sql
DECLARE @SQL NVARCHAR(MAX)
DECLARE @Count INT
SET @SQL = N'SELECT @CountOut = COUNT(*) FROM Employees WHERE Salary > @MinSalary'
EXEC sp_executesql
@SQL,
N'@MinSalary DECIMAL(10,2), @CountOut INT OUTPUT',
@MinSalary = 50000,
@CountOut = @Count OUTPUT
SELECT @Count AS 'EmployeeCount'
4. 返回结果集
sql
DECLARE @SQL NVARCHAR(MAX) DECLARE @MinAge INT = 25 SET @SQL = N'SELECT Name, Age FROM Users WHERE Age > @Age' -- 直接返回结果集 EXEC sp_executesql @SQL, N'@Age INT', @Age = @MinAge
语法结构
sql
sp_executesql
@stmt = N'SQL语句', -- 要执行的 SQL
@params = N'参数定义', -- 参数定义（可选）
@param1 = '值1', -- 参数值
@param2 = '值2', -- 参数值
... -- 更多参数
实际应用场景
1. 动态表名查询
sql
CREATE PROCEDURE GetTableData
@TableName NVARCHAR(100),
@WhereClause NVARCHAR(1000) = NULL
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'SELECT * FROM ' + QUOTENAME(@TableName)
IF @WhereClause IS NOT NULL
SET @SQL = @SQL + ' WHERE ' + @WhereClause
EXEC sp_executesql @SQL
END
2. 动态排序
sql
DECLARE @SQL NVARCHAR(MAX)
DECLARE @SortColumn NVARCHAR(50) = 'LastName'
DECLARE @SortOrder NVARCHAR(4) = 'ASC'
SET @SQL = N'SELECT * FROM Employees ORDER BY ' +
QUOTENAME(@SortColumn) + ' ' + @SortOrder
EXEC sp_executesql @SQL
注意事项
-
使用 NVARCHAR：SQL 语句必须是
NVARCHAR类型 -
参数类型匹配：确保参数定义和实际值类型一致
-
QUOTENAME 函数：动态对象名使用
QUOTENAME()防止注入 -
性能考虑：复杂的动态 SQL 可能影响性能
sp_executesql 是构建安全、高效动态 SQL 的重要工具，特别适用于需要参数化和执行计划重用的场景。
请详细讲解这行代码：
exec sp_executesql @FinalSelectSql, N'@ResultSelectDynamicSql nvarchar(max) output', @ResultSelectDynamicSqlOutput output
这行代码是使用 sp_executesql 执行动态 SQL 并获取输出参数的典型用法。让我详细分解每个部分：
代码结构分解
sql
exec sp_executesql
@FinalSelectSql, -- 第1部分：要执行的动态SQL
N'@ResultSelectDynamicSql nvarchar(max) output', -- 第2部分：参数定义
@ResultSelectDynamicSqlOutput output -- 第3部分：参数赋值和输出
1. 第1部分：动态 SQL 语句
sql
@FinalSelectSql
-
这是一个包含实际要执行的 SQL 代码的变量
-
例子：
sql
-- @FinalSelectSql 可能包含这样的内容：
SET @FinalSelectSql = '
SELECT @ResultSelectDynamicSql =
''SELECT [ID], [Name]'' +
(SELECT '',['' + ColumnName + '']'' FROM DynamicColumns FOR XML PATH(''''))
'
2. 第2部分：参数定义
sql
N'@ResultSelectDynamicSql nvarchar(max) output'
-
N'...'：表示 Unicode 字符串 -
@ResultSelectDynamicSql：在动态 SQL 内部使用的参数名 -
nvarchar(max)：参数的数据类型 -
output：标记这个参数是输出参数（可以从动态 SQL 返回值）
3. 第3部分：参数赋值和输出
sql
@ResultSelectDynamicSqlOutput output
-
@ResultSelectDynamicSqlOutput：外部变量，用于接收返回值 -
output：表示这个外部变量要接收动态 SQL 的输出值
完整执行流程示例
步骤1：准备数据
sql
-- 假设我们有一个动态列的表
CREATE TABLE DynamicColumns (ColumnName NVARCHAR(50))
INSERT INTO DynamicColumns VALUES ('Age'), ('Salary'), ('Department')
步骤2：构建动态 SQL
sql
DECLARE @FinalSelectSql NVARCHAR(MAX) = '
-- 在动态SQL内部构建复杂的SELECT语句
DECLARE @ColumnList NVARCHAR(MAX) = ''''
SELECT @ColumnList = @ColumnList + '', ['' + ColumnName + '']''
FROM DynamicColumns
-- 将结果赋值给输出参数
SET @ResultSelectDynamicSql = ''SELECT [ID], [Name]'' + @ColumnList
'
DECLARE @ResultSelectDynamicSqlOutput NVARCHAR(MAX)
步骤3：执行并获取结果
sql
exec sp_executesql
@FinalSelectSql,
N'@ResultSelectDynamicSql nvarchar(max) output',
@ResultSelectDynamicSqlOutput output
-- 查看结果
SELECT @ResultSelectDynamicSqlOutput AS '生成的SQL'
执行结果：
text
生成的SQL ------------------------------------------- SELECT [ID], [Name], [Age], [Salary], [Department]
参数传递机制详解
内存中的变化：
text
执行前： @ResultSelectDynamicSqlOutput = NULL 执行中（在动态SQL内部）： @ResultSelectDynamicSql = 'SELECT [ID], [Name], [Age], [Salary], [Department]' 执行后： @ResultSelectDynamicSqlOutput = 'SELECT [ID], [Name], [Age], [Salary], [Department]'
实际应用场景
场景1：动态报表列生成
sql
-- 根据用户选择的列动态生成SQL
DECLARE @DynamicSQL NVARCHAR(MAX) = '
SET @ResultSelectDynamicSql = ''SELECT ''
IF @IncludeName = 1
SET @ResultSelectDynamicSql = @ResultSelectDynamicSql + ''[Name], ''
IF @IncludeSalary = 1
SET @ResultSelectDynamicSql = @ResultSelectDynamicSql + ''[Salary], ''
-- 去除最后的逗号
SET @ResultSelectDynamicSql = LEFT(@ResultSelectDynamicSql, LEN(@ResultSelectDynamicSql) - 1) + '' FROM Employees''
'
DECLARE @IncludeName BIT = 1, @IncludeSalary BIT = 1
DECLARE @GeneratedSQL NVARCHAR(MAX)
EXEC sp_executesql
@DynamicSQL,
N'@ResultSelectDynamicSql nvarchar(max) output, @IncludeName bit, @IncludeSalary bit',
@ResultSelectDynamicSql = @GeneratedSQL output,
@IncludeName = @IncludeName,
@IncludeSalary = @IncludeSalary
SELECT @GeneratedSQL -- 结果: 'SELECT [Name], [Salary] FROM Employees'
场景2：复杂条件构建
sql
DECLARE @ConditionBuilder NVARCHAR(MAX) = '
DECLARE @WhereClause NVARCHAR(MAX) = ''''
IF @Department IS NOT NULL
SET @WhereClause = @WhereClause + '' AND Department = '''''' + @Department + '''''' ''
IF @MinSalary IS NOT NULL
SET @WhereClause = @WhereClause + '' AND Salary >= '' + CAST(@MinSalary AS NVARCHAR)
SET @ResultSelectDynamicSql = ''SELECT * FROM Employees WHERE 1=1 '' + @WhereClause
'
DECLARE @Department NVARCHAR(50) = 'IT', @MinSalary DECIMAL = 50000
DECLARE @FinalSQL NVARCHAR(MAX)
EXEC sp_executesql
@ConditionBuilder,
N'@ResultSelectDynamicSql nvarchar(max) output, @Department nvarchar(50), @MinSalary decimal',
@ResultSelectDynamicSql = @FinalSQL output,
@Department = @Department,
@MinSalary = @MinSalary
SELECT @FinalSQL
-- 结果: 'SELECT * FROM Employees WHERE 1=1 AND Department = 'IT' AND Salary >= 50000'
关键要点总结
-
参数名对应：第二个参数中的
@ResultSelectDynamicSql与动态 SQL 内部的参数名必须一致 -
数据类型匹配：定义的数据类型必须与实际使用的类型匹配
-
OUTPUT 关键字：在定义和调用时都需要
output关键字 -
作用域分离：动态 SQL 内部的变量与外部变量是独立的，通过参数传递连接
-
Unicode 字符串：参数定义必须使用
N'...'格式
这种机制非常适合需要在动态 SQL 中构建复杂字符串并返回结果的场景！
详细讲解以下代码的意思，以及运行机制：
declare @ResultSelectSql = 'select [自编号], [样品名称], [样品来源]'
declare @ItemNameSQL = '
declare @item_names table (rvt_ItemName nvarchar(max))
insert into @item_names
select rvt_ItemName from ##Result_B52B7261BFCD4AB991B8C6145566F3CF group by SortNo,rvt_ItemName order by SortNo,rvt_ItemName
declare @item_sql nvarchar(max) = ''''
select @item_sql = @item_sql + '',['' + rvt_ItemName + ''] = max(case rvt_ItemName when '''''''' + rvt_ItemName + '''''''' then rvt_Result else ''''----'''' end) ''
from @item_names
set @ResultSelectSql = @ResultSelectSql + @item_sql
'
declare @FullSQL nvarchar(max) = '
set @ResultSelectSql = '''
+ @ResultSelectSql + ''''
+ @ItemNameSQL
exec sp_executesql @FullSQL, N'@ResultSelectSql nvarchar(max) output', @ResultSelectSql output
这是一个动态构建列的复杂SQL代码。
代码整体目标
这段代码的目的是动态生成一个包含固定列和动态列的SELECT语句。
第一步：初始化基础SQL
sql
declare @ResultSelectSql nvarchar(max) = 'select [自编号], [样品名称], [样品来源]'
-
创建一个变量
@ResultSelectSql -
初始值为固定的SELECT语句开头部分
-
包含3个固定列：
[自编号],[样品名称],[样品来源]
第二步：定义动态列构建逻辑
sql
declare @ItemNameSQL nvarchar(max) = ' declare @item_names table (rvt_ItemName nvarchar(max)) insert into @item_names select rvt_ItemName from ##Result_B52B7261BFCD4AB991B8C6145566F3CF group by SortNo,rvt_ItemName order by SortNo,rvt_ItemName declare @item_sql nvarchar(max) = '''' select @item_sql = @item_sql + '',['' + rvt_ItemName + ''] = max(case rvt_ItemName when '''''''' + rvt_ItemName + '''''''' then rvt_Result else ''''----'''' end) '' from @item_names set @ResultSelectSql = @ResultSelectSql + @item_sql '
详细分解这部分：
2.1 创建临时表并插入数据
sql
declare @item_names table (rvt_ItemName nvarchar(max)) insert into @item_names select rvt_ItemName from ##Result_B52B7261BFCD4AB991B8C6145566F3CF group by SortNo, rvt_ItemName order by SortNo, rvt_ItemName
-
创建一个表变量
@item_names，用于存储唯一的项目名称 -
从全局临时表
##Result_B52B7261BFCD4AB991B8C6145566F3CF中获取不重复的rvt_ItemName -
按
SortNo和rvt_ItemName排序
2.2 构建动态列字符串
sql
declare @item_sql nvarchar(max) = '''' select @item_sql = @item_sql + '',['' + rvt_ItemName + ''] = max(case rvt_ItemName when '''''''' + rvt_ItemName + '''''''' then rvt_Result else ''''----'''' end) '' from @item_names
这是最复杂的部分，让我用实际例子说明：
假设表中有3个项目名称：
-
项目A
-
项目B
-
项目C
生成的SQL会是：
sql
,[项目A] = max(case rvt_ItemName when '项目A' then rvt_Result else '----' end) ,[项目B] = max(case rvt_ItemName when '项目B' then rvt_Result else '----' end) ,[项目C] = max(case rvt_ItemName when '项目C' then rvt_Result else '----' end)
2.3 拼接最终SQL
sql
set @ResultSelectSql = @ResultSelectSql + @item_sql
把动态生成的列追加到基础SELECT语句后面。
第三步：构建完整执行语句
sql
declare @FullSQL nvarchar(max) = ' set @ResultSelectSql = ''' + @ResultSelectSql + '''' + @ItemNameSQL
这相当于构建了这样的代码：
sql
set @ResultSelectSql = 'select [自编号], [样品名称], [样品来源]' -- 然后执行 @ItemNameSQL 中的动态列生成逻辑
第四步：执行动态SQL
sql
exec sp_executesql @FullSQL, N'@ResultSelectSql nvarchar(max) output', @ResultSelectSql output
完整执行流程示例
执行前：
sql
@ResultSelectSql = 'select [自编号], [样品名称], [样品来源]'
执行后（假设有3个动态项目）：
sql
@ResultSelectSql = 'select [自编号], [样品名称], [样品来源] ,[项目A] = max(case rvt_ItemName when '项目A' then rvt_Result else '----' end) ,[项目B] = max(case rvt_ItemName when '项目B' then rvt_Result else '----' end) ,[项目C] = max(case rvt_ItemName when '项目C' then rvt_Result else '----' end)'
实际应用场景
这种代码通常用于动态报表或数据透视，比如：
原始数据格式：
text
样品名称 | 项目名称 | 检测结果 样品1 | 项目A | 合格 样品1 | 项目B | 不合格 样品1 | 项目C | 合格 样品2 | 项目A | 合格
转换为：
text
样品名称 | 项目A | 项目B | 项目C 样品1 | 合格 | 不合格 | 合格 样品2 | 合格 | ---- | ----
关键点总结
-
动态列生成：根据数据内容自动创建列
-
行转列：使用
CASE WHEN实现数据透视 -
字符串拼接：通过循环拼接构建复杂SQL
-
执行计划：使用
sp_executesql安全执行动态SQL
这种技术在构建灵活的数据报表系统时非常有用！
代码中，多处出现的@ResultSelectSql，感觉好混乱，请再详细帮忙梳理
这段代码中多处出现的 @ResultSelectSql 确实容易让人混淆。让我重新梳理这些变量的作用域和生命周期：
变量作用域分析
实际上这里存在两个不同作用域的 @ResultSelectSql 变量：
1. 外层变量（主作用域）
sql
-- 这是在外层声明的变量 declare @ResultSelectSql nvarchar(max) = 'select [自编号], [样品名称], [样品来源]'
2. 内层变量（动态SQL作用域）
sql
-- 这是在动态SQL内部使用的同名变量 -- 通过 sp_executesql 的参数传递
执行过程逐步分析
让我用颜色标记来区分：
第一步：初始化
sql
-- 🟦外层变量：初始值 declare @ResultSelectSql nvarchar(max) = 'select [自编号], [样品名称], [样品来源]'
第二步：构建动态SQL逻辑
sql
declare @ItemNameSQL nvarchar(max) = ' declare @item_names table (rvt_ItemName nvarchar(max)) insert into @item_names select rvt_ItemName from ##Result_B52B7261BFCD4AB991B8C6145566F3CF group by SortNo,rvt_ItemName order by SortNo,rvt_ItemName declare @item_sql nvarchar(max) = '''' select @item_sql = @item_sql + '',['' + rvt_ItemName + ''] = max(case rvt_ItemName when '''''''' + rvt_ItemName + '''''''' then rvt_Result else ''''----'''' end) '' from @item_names -- 🟩内层变量：在动态SQL内部使用 set @ResultSelectSql = @ResultSelectSql + @item_sql '
第三步：拼接完整执行语句
sql
declare @FullSQL nvarchar(max) = ' -- 🟩将🟦外层变量的值赋给🟩内层变量 set @ResultSelectSql = ''' + @ResultSelectSql + '''' + @ItemNameSQL
第四步：执行并传回结果
sql
-- 🟦外层变量 ← 🟩内层变量（通过output参数） exec sp_executesql @FullSQL, N'@ResultSelectSql nvarchar(max) output', @ResultSelectSql output
实际内存变化过程
执行前：
text
🟦外层 @ResultSelectSql = 'select [自编号], [样品名称], [样品来源]'
执行中（在动态SQL内部）：
text
🟩内层 @ResultSelectSql = 'select [自编号], [样品名称], [样品来源]' ↓ （经过动态列拼接） 🟩内层 @ResultSelectSql = 'select [自编号], [样品名称], [样品来源], [项目A] = max(...), [项目B] = max(...)'
执行后：
text
🟦外层 @ResultSelectSql = 'select [自编号], [样品名称], [样品来源], [项目A] = max(...), [项目B] = max(...)'
更清晰的改写版本
为了避免混淆，可以这样改写：
sql
-- 外层变量改名为更清晰的名字 declare @BaseSelectSql nvarchar(max) = 'select [自编号], [样品名称], [样品来源]' declare @FinalSelectSql nvarchar(max) = '' declare @ItemNameSQL nvarchar(max) = ' declare @item_names table (rvt_ItemName nvarchar(max)) insert into @item_names select rvt_ItemName from ##Result_B52B7261BFCD4AB991B8C6145566F3CF group by SortNo,rvt_ItemName order by SortNo,rvt_ItemName declare @item_sql nvarchar(max) = '''' select @item_sql = @item_sql + '',['' + rvt_ItemName + ''] = max(case rvt_ItemName when '''''''' + rvt_ItemName + '''''''' then rvt_Result else ''''----'''' end) '' from @item_names -- 使用不同的变量名避免混淆 set @DynamicSelectSql = @BaseSelectSql + @item_sql ' declare @FullSQL nvarchar(max) = ' set @DynamicSelectSql = ''' + @BaseSelectSql + '''' + @ItemNameSQL -- 参数名也对应修改 exec sp_executesql @FullSQL, N'@DynamicSelectSql nvarchar(max) output', @FinalSelectSql output
关键理解点
-
同名但不同作用域：外层和内层的
@ResultSelectSql是两个不同的变量 -
值传递：通过
sp_executesql的参数机制，外层变量的值传递给内层变量 -
结果回传：内层处理完后，通过
OUTPUT参数将结果传回外层变量 -
生命周期：内层变量在动态SQL执行结束后就销毁了
简单类比
可以把这个过程想象成：
-
你（外层）写了一张纸条给助手（内层）
-
助手在纸条上添加内容
-
助手把修改后的纸条还给你
虽然都叫"纸条"，但经过助手处理后，内容已经发生了变化。
这样理解应该就不混乱了！
sp_executesql 和 EXEC() 都是 SQL Server 中执行动态 SQL 的方法，但它们有重要的区别。让我详细对比：
1. 基本语法对比
EXEC() (或 EXECUTE())
sql
DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM Employees WHERE Department = ''IT''' EXEC(@SQL)
sp_executesql
sql
DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM Employees WHERE Department = @Dept' EXEC sp_executesql @SQL, N'@Dept NVARCHAR(50)', @Dept = 'IT'
2. 主要区别详解
2.1 参数化支持（最重要的区别）
sp_executesql - 支持参数化
sql
DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM Employees WHERE Salary > @MinSalary AND Department = @Dept'
EXEC sp_executesql @SQL,
N'@MinSalary DECIMAL(10,2), @Dept NVARCHAR(50)',
@MinSalary = 50000,
@Dept = 'IT'
EXEC() - 不支持参数化，必须拼接字符串
sql
DECLARE @MinSalary DECIMAL(10,2) = 50000 DECLARE @Dept NVARCHAR(50) = 'IT' DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM Employees WHERE Salary > ' + CAST(@MinSalary AS NVARCHAR) + ' AND Department = ''' + @Dept + '''' EXEC(@SQL)
2.2 SQL 注入安全性
sp_executesql - 安全
sql
-- 即使用户输入恶意代码，也不会执行 DECLARE @UserInput NVARCHAR(100) = 'IT''; DROP TABLE Employees--' DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM Employees WHERE Department = @Dept' EXEC sp_executesql @SQL, N'@Dept NVARCHAR(100)', @Dept = @UserInput -- 安全：只会查询，不会删除表
EXEC() - 不安全
sql
DECLARE @UserInput NVARCHAR(100) = 'IT''; DROP TABLE Employees--' DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM Employees WHERE Department = ''' + @UserInput + '''' EXEC(@SQL) -- 危险：会执行 DROP TABLE 语句！
2.3 执行计划重用
sp_executesql - 可以重用执行计划
sql
DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM Products WHERE CategoryID = @CatID' -- 第一次执行 EXEC sp_executesql @SQL, N'@CatID INT', @CatID = 1 -- 生成执行计划 -- 第二次执行 - 重用相同的执行计划 EXEC sp_executesql @SQL, N'@CatID INT', @CatID = 2 -- 性能更好
EXEC() - 每次都是新的执行计划
sql
-- 第一次执行 DECLARE @SQL1 NVARCHAR(MAX) = 'SELECT * FROM Products WHERE CategoryID = 1' EXEC(@SQL1) -- 生成执行计划1 -- 第二次执行 DECLARE @SQL2 NVARCHAR(MAX) = 'SELECT * FROM Products WHERE CategoryID = 2' EXEC(@SQL2) -- 生成执行计划2（不能重用）
2.4 输出参数支持
sp_executesql - 支持输出参数
sql
DECLARE @SQL NVARCHAR(MAX) = 'SELECT @Count = COUNT(*) FROM Employees' DECLARE @EmployeeCount INT EXEC sp_executesql @SQL, N'@Count INT OUTPUT', @Count = @EmployeeCount OUTPUT SELECT @EmployeeCount AS '员工总数'
EXEC() - 不支持输出参数
sql
-- 无法直接获取返回值，需要借助临时表 CREATE TABLE #Temp (Count INT) DECLARE @SQL NVARCHAR(MAX) = 'INSERT INTO #Temp SELECT COUNT(*) FROM Employees' EXEC(@SQL) SELECT * FROM #Temp DROP TABLE #Temp
2.5 性能对比
| 场景 | sp_executesql | EXEC() |
|---|---|---|
| 多次执行相同模式 | ✅ 执行计划重用，性能好 | ❌ 每次重新编译，性能差 |
| 处理用户输入 | ✅ 参数化，安全高效 | ❌ 字符串拼接，风险高 |
| 复杂动态SQL | ✅ 结构清晰，易于维护 | ❌ 拼接复杂，难以调试 |
3. 实际应用建议
使用 sp_executesql 的场景：
sql
-- 1. 需要参数化的查询 DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM Orders WHERE OrderDate BETWEEN @Start AND @End' EXEC sp_executesql @SQL, N'@Start DATE, @End DATE', @Start = '2023-01-01', @End = '2023-12-31' -- 2. 需要输出参数的场景 DECLARE @SQL NVARCHAR(MAX) = 'SELECT @MaxSalary = MAX(Salary) FROM Employees' DECLARE @MaxSalary DECIMAL(10,2) EXEC sp_executesql @SQL, N'@MaxSalary DECIMAL(10,2) OUTPUT', @MaxSalary OUTPUT -- 3. 频繁执行的动态SQL DECLARE @SQL NVARCHAR(MAX) = 'UPDATE Products SET Price = @Price WHERE ProductID = @ID' EXEC sp_executesql @SQL, N'@Price DECIMAL(10,2), @ID INT', @Price = 19.99, @ID = 1 EXEC sp_executesql @SQL, N'@Price DECIMAL(10,2), @ID INT', @Price = 29.99, @ID = 2
使用 EXEC() 的场景：
sql
-- 1. 简单的动态表名/列名（使用 QUOTENAME 防止注入） DECLARE @TableName NVARCHAR(100) = 'Employees' DECLARE @SQL NVARCHAR(MAX) = 'SELECT TOP 10 * FROM ' + QUOTENAME(@TableName) EXEC(@SQL) -- 2. 执行存储的动态SQL（从表或变量中读取） DECLARE @SQLCommand NVARCHAR(MAX) SELECT @SQLCommand = SQLText FROM SavedQueries WHERE QueryID = 1 EXEC(@SQLCommand) -- 3. 简单的一次性动态SQL DECLARE @SQL NVARCHAR(MAX) = 'BACKUP DATABASE MyDB TO DISK = ''D:\backup.bak''' EXEC(@SQL)
4. 总结
| 特性 | sp_executesql | EXEC() |
|---|---|---|
| 参数化支持 | ✅ 支持 | ❌ 不支持 |
| SQL注入防护 | ✅ 安全 | ❌ 危险 |
| 执行计划重用 | ✅ 可以重用 | ❌ 不能重用 |
| 输出参数 | ✅ 支持 | ❌ 不支持 |
| 性能 | ✅ 更好 | ❌ 较差 |
| 代码可读性 | ✅ 更清晰 | ❌ 拼接混乱 |
| 使用场景 | 复杂、频繁、安全的动态SQL | 简单、一次性、无用户输入的动态SQL |
推荐优先使用 sp_executesql，除非是简单的、不需要参数化的场景。
实例比对
使用 exec() 执行动态Sql
-- ===========================================================================
-- 并发优化【关键修改】：构建【最终查询：返回数据】Sql
-- ===========================================================================
declare @ResultSelectSql nvarchar(1000)
set @ResultSelectSql = '
select [自编号], [样品名称], [样品来源]
'
declare @ItemNameRowToColDynamicSql nvarchar(1000)
set @ItemNameRowToColDynamicSql = '
declare @ItemNames table (rvt_ItemName nvarchar(200))
insert into @ItemNames
select rvt_ItemName from ##Result_7BEE9C3BE81946F7B3EF7E0F70F5686B group by SortNo,rvt_ItemName order by SortNo,rvt_ItemName
declare @ItemNameSql nvarchar(max) = ''
select @ItemNameSql = @ItemNameSql + '',['' + rvt_ItemName + ''] = max(case rvt_ItemName when '''''''' + rvt_ItemName + '''''''' then rvt_Result else ''''----'''' end) ''
from @ItemNames
'
-- ===========================================================================
-- 方案一：使用 exec() 执行动态Sql，通过临时表获取返回数据
-- 1、创建临时表
-- 2、拼接动态Sql逻辑：构建【最终查询：返回数据】Sql
-- 3、使用 exec() 执行动态Sql
-- 4、通过临时表获取【最终查询：返回数据】Sql
-- 5、删除临时表
-- ===========================================================================
-- 1、创建临时表
create table #ResultSelectSqlTempTable (sql_command nvarchar(max))
-- 2、拼接动态Sql逻辑：构建【最终查询：返回数据】Sql
-- 2.1、declare @ResultSelectDynamicSql nvarchar(max) => 定义变量 @ResultSelectDynamicSql
-- 2.2、set @ResultSelectDynamicSql = ''' + @ResultSelectSql + '''' => 给变量赋值，初始值
-- 2.3、+ @ItemNameRowToColDynamicSql + ' => 执行这段动态sql，行转列，获取项目名称
-- 2.4、set @ResultSelectDynamicSql = @ResultSelectDynamicSql + @ItemNameSql => 给变量赋值，最终内容
-- 2.5、insert into #ResultSelectSqlTempTable values (@ResultSelectDynamicSql) => 将最终内容插入临时表
declare @FinalSelectSql nvarchar(max) = '
declare @ResultSelectDynamicSql nvarchar(max)
set @ResultSelectDynamicSql = ''' + @ResultSelectSql + ''''
+ @ItemNameRowToColDynamicSql + '
set @ResultSelectDynamicSql = @ResultSelectDynamicSql + @ItemNameSql
insert into #ResultSelectSqlTempTable values (@ResultSelectDynamicSql)
'
-- 3、使用 exec() 执行动态Sql
exec(@FinalSelectSql)
-- 4、再从临时表中查询数据，从而获取返回数据
select @ResultSelectSql = sql_command from #ResultSelectSqlTempTable
-- 5、删除临时表
drop table #ResultSelectSqlTempTable
使用 exec sp_executesql 执行动态Sql
-- ===========================================================================
-- 并发优化【关键修改】：构建【最终查询：返回数据】Sql
-- ===========================================================================
declare @ResultSelectSql nvarchar(1000)
set @ResultSelectSql = '
select [自编号], [样品名称], [样品来源]
'
declare @ItemNameRowToColDynamicSql nvarchar(1000)
set @ItemNameRowToColDynamicSql = '
declare @ItemNames table (rvt_ItemName nvarchar(200))
insert into @ItemNames
select rvt_ItemName from ##Result_7BEE9C3BE81946F7B3EF7E0F70F5686B group by SortNo,rvt_ItemName order by SortNo,rvt_ItemName
declare @ItemNameSql nvarchar(max) = ''
select @ItemNameSql = @ItemNameSql + '',['' + rvt_ItemName + ''] = max(case rvt_ItemName when '''''''' + rvt_ItemName + '''''''' then rvt_Result else ''''----'''' end) ''
from @ItemNames
'
-- ===========================================================================
-- 方案二：使用 exec sp_executesql 执行动态Sql，通过动态Sql的输出值获取返回数据
-- 1、拼接动态Sql逻辑：构建【最终查询：返回数据】Sql
-- 2、使用 exec sp_executesql 执行动态Sql
-- 3、通过动态Sql的输出值获取【最终查询：返回数据】Sql
-- ===========================================================================
-- 1、拼接动态Sql逻辑：构建【最终查询：返回数据】Sql
-- 1.1、set @ResultSelectDynamicSql = ''' + @ResultSelectSql + '''' => 给参数赋值，初始值
-- 1.2、+ @ItemNameRowToColDynamicSql + ' => 执行这段动态sql，行转列，获取项目名称
-- 1.3、set @ResultSelectDynamicSql = @ResultSelectDynamicSql + @ItemNameSql => 给参数赋值，最终内容
declare @FinalSelectSql nvarchar(1000) = '
set @ResultSelectDynamicSql = ''' + @ResultSelectSql + ''''
+ @ItemNameRowToColDynamicSql + '
set @ResultSelectDynamicSql = @ResultSelectDynamicSql + @ItemNameSql
'
-- 2、使用 exec sp_executesql 执行动态Sql
-- 2.1、定义动态Sql内部参数（输出参数） @ResultSelectDynamicSql（与动态Sql内部的参数名 @ResultSelectDynamicSql 必须一致）
-- 2.2、定义外部变量 @ResultSelectDynamicSqlOutput，用于接收返回值（动态Sql的输出值）
declare @ResultSelectDynamicSqlOutput nvarchar(1000)
exec sp_executesql @FinalSelectSql, N'@ResultSelectDynamicSql nvarchar(1000) output', @ResultSelectDynamicSqlOutput output
-- 3、通过动态Sql的输出值获取【最终查询：返回数据】Sql
set @ResultSelectSql = @ResultSelectDynamicSqlOutput
代码封装
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[proc_RowToColGenerateDynamicItemNames]') and objectproperty(id, N'IsProcedure') = 1)
drop procedure [dbo].[proc_RowToColGenerateDynamicItemNames]
go
set ansi_nulls on
go
set quoted_identifier on
go
/****************************************************************************/
/* Author :杨广标 */
/* Date :2025-11-17 */
/* version:1.0 */
/* description:行转列生成动态项目名称（构建动态列） */
/****************************************************************************/
-- ===========================================================================
-- 功能说明：
-- 1. 将表（会话表）的数据行转列，构建动态的项目名称列字段
-- 2. 项目列名与检验结果格式、项目单位显示类型有关联
-- 3. 处理空值
-- 4. 输出项目名称动态列Sql
-- 5. 应用示例
-- exec proc_RowToColGenerateDynamicItemNames '##Result_F7744127A6084D77988CBF2A36B64B75', 0, 0, '---', @FieldSql output
-- 6. 输出数据样式：
-- ,[细菌总数] = max(case rvt_ItemName when '' + rvt_ItemName + '' then rvt_Result else '---' end)
-- ,[大肠菌群] = max(case rvt_ItemName when '' + rvt_ItemName + '' then rvt_Result else '---' end)
-- ,[金黄色葡萄球菌] = max(case rvt_ItemName when '' + rvt_ItemName + '' then rvt_Result else '---' end)
-- ===========================================================================
create procedure [dbo].[proc_RowToColGenerateDynamicItemNames]
@SessionResultTable varchar(100), -- 会话表
@TableContentType int, -- 检验结果格式
@VerifyItemUnitType int, -- 项目单位显示类型
@NullMark nvarchar(50), -- 空值显示的内容
@ItemNameDynamiFieldsSql nvarchar(max) output -- 项目名称动态字段Sql
as
begin
set nocount on
declare @ItemNameRowToColDynamicSql nvarchar(max) -- 项目名称行转列动态Sql
set @ItemNameDynamiFieldsSql = isnull(@ItemNameDynamiFieldsSql, '')
-- 不需要显示标准值的情况（不出具方法、标准值和结论、出具结论）
if @TableContentType in(0,2)
begin
-- 检测项目后面接单位
if @VerifyItemUnitType in (1,5,7,8)
begin
set @ItemNameRowToColDynamicSql = '
declare @ItemNames table (rvt_ItemName nvarchar(200), rvt_Unit nvarchar(100))
insert into @ItemNames
select rvt_ItemName,Rvt_Unit from ' + @SessionResultTable + ' group by SortNo,rvt_ItemName,Rvt_Unit order by SortNo,rvt_ItemName,Rvt_Unit
declare @ItemNameSql nvarchar(max) = ''''
select @ItemNameSql = @ItemNameSql + '',['' + rvt_ItemName + ''] = max(case rvt_ItemName when '''''''' + rvt_ItemName + '''''''' then rvt_Result else
'
end
-- 检测项目后面不接单位
else
begin
set @ItemNameRowToColDynamicSql = '
declare @ItemNames table (rvt_ItemName nvarchar(200))
insert into @ItemNames
select rvt_ItemName from ' + @SessionResultTable + ' group by SortNo,rvt_ItemName order by SortNo,rvt_ItemName
declare @ItemNameSql nvarchar(max) = ''''
select @ItemNameSql = @ItemNameSql + '',['' + rvt_ItemName + ''] = max(case rvt_ItemName when '''''''' + rvt_ItemName + '''''''' then rvt_Result else
'
end
end
-- 需要显示标准值的情况
else
begin
-- 检测项目后面接单位
if @VerifyItemUnitType in (1,5,7,8)
begin
set @ItemNameRowToColDynamicSql = '
declare @ItemNames table (rvt_ItemName nvarchar(200), rvt_Unit nvarchar(100), vsiID varchar(10))
insert into @ItemNames
select rvt_ItemName from ' + @SessionResultTable + ' group by SortNo,rvt_ItemName,Rvt_Unit,vsiID order by SortNo,rvt_ItemName,Rvt_Unit,vsiID
declare @ItemNameSql nvarchar(max) = ''''
select @ItemNameSql = @ItemNameSql + '',['' + rvt_ItemName + ''] = max(case vsiID when '''''''' + vsiID + '''''''' then rvt_Result else
'
end
-- 检测项目后面不接单位
else
begin
set @ItemNameRowToColDynamicSql = '
declare @ItemNames table (rvt_ItemName nvarchar(200), vsiID varchar(10))
insert into @ItemNames
select rvt_ItemName from ' + @SessionResultTable + ' group by SortNo,rvt_ItemName,vsiID order by SortNo,rvt_ItemName,vsiID
declare @ItemNameSql nvarchar(max) = ''''
select @ItemNameSql = @ItemNameSql + '',['' + rvt_ItemName + ''] = max(case vsiID when '''''''' + vsiID + '''''''' then rvt_Result else
'
end
end
-- 空值显示的内容
if @NullMark is null
set @ItemNameRowToColDynamicSql = @ItemNameRowToColDynamicSql + 'null '
else
set @ItemNameRowToColDynamicSql = @ItemNameRowToColDynamicSql + '''''' + @NullMark + ''''' '
set @ItemNameRowToColDynamicSql = @ItemNameRowToColDynamicSql + ' end) ''
from @ItemNames
'
-- =============================================================================
-- 构建动态列：行转列生成动态项目名称
-- 输出数据样式：
-- ,[细菌总数] = max(case rvt_ItemName when '' + rvt_ItemName + '' then rvt_Result else '---' end)
-- ,[大肠菌群] = max(case rvt_ItemName when '' + rvt_ItemName + '' then rvt_Result else '---' end)
-- ,[金黄色葡萄球菌] = max(case rvt_ItemName when '' + rvt_ItemName + '' then rvt_Result else '---' end)
-- =============================================================================
-- 项目名称行转列动态Sql数据样式
/*
set @ItemNameRowToColDynamicSql = '
declare @ItemNames table (rvt_ItemName nvarchar(200))
insert into @ItemNames
select rvt_ItemName from ##Result_7BEE9C3BE81946F7B3EF7E0F70F5686B group by SortNo,rvt_ItemName order by SortNo,rvt_ItemName
declare @ItemNameSql nvarchar(max) = ''
select @ItemNameSql = @ItemNameSql + '',['' + rvt_ItemName + ''] = max(case rvt_ItemName when '''''''' + rvt_ItemName + '''''''' then rvt_Result else ''''----'''' end) ''
from @ItemNames
'
*/
-- =============================================================================
-- 方案一：使用 exec() 执行动态Sql，通过临时表获取返回数据
-- 1、创建临时表
-- 2、拼接动态Sql逻辑：构建【构建动态列：行转列生成动态项目名称】Sql
-- 3、使用 exec() 执行动态Sql
-- 4、通过临时表获取【构建动态列：行转列生成动态项目名称】Sql
-- 5、删除临时表
-- =============================================================================
-- 1、创建临时表
create table #temp (field_sql nvarchar(max))
/*
-- 2、拼接动态Sql逻辑：构建【构建动态列：行转列生成动态项目名称】Sql
-- 2.1、declare @ItemNameFieldsSql nvarchar(max) => 定义变量 @ItemNameFieldsSql
-- 2.2、set @ItemNameFieldsSql = ''' + @ItemNameDynamiFieldsSql + '''' => 给变量赋值，初始值
-- 2.3、+ @ItemNameRowToColDynamicSql + ' => 执行这段动态sql，行转列，获取项目名称
-- 2.4、set @ItemNameFieldsSql = @ItemNameFieldsSql + @ItemNameSql => 给变量赋值，最终内容
-- 2.5、insert into #temp values (@ItemNameFieldsSql) => 将最终内容插入临时表
declare @FinalExecSql nvarchar(1000) = '
declare @ItemNameFieldsSql nvarchar(max)
set @ItemNameFieldsSql = ''' + @ItemNameDynamiFieldsSql + ''''
+ @ItemNameRowToColDynamicSql + '
set @ItemNameFieldsSql = @ItemNameFieldsSql + @ItemNameSql
insert into #temp values (@ItemNameFieldsSql)
'
-- 3、使用 exec() 执行动态Sql
exec(@FinalExecSql)
-- 4、再从临时表中查询数据，从而获取返回数据
select @ItemNameDynamiFieldsSql = field_sql from #temp
-- 5、删除临时表
drop table #temp
*/
-- =============================================================================
-- 方案二：使用 exec sp_executesql 执行动态Sql，通过动态Sql的输出值获取返回数据
-- 1、拼接动态Sql逻辑：构建【构建动态列：行转列生成动态项目名称】Sql
-- 2、使用 exec sp_executesql 执行动态Sql
-- 3、通过动态Sql的输出值获取【构建动态列：行转列生成动态项目名称】Sql
-- =============================================================================
-- 1、拼接动态Sql逻辑：构建【构建动态列：行转列生成动态项目名称】Sql
-- 1.1、set @ItemNameFieldsSql = ''' + @ItemNameDynamiFieldsSql + '''' => 给参数赋值，初始值
-- 1.2、+ @ItemNameRowToColDynamicSql + ' => 执行这段动态sql，行转列，获取项目名称
-- 1.3、set @ItemNameFieldsSql = @ItemNameFieldsSql + @ItemNameSql => 给参数赋值，最终内容
declare @FinalExecSql nvarchar(1000) = '
set @ItemNameFieldsSql = ''' + @ItemNameDynamiFieldsSql + ''''
+ @ItemNameRowToColDynamicSql + '
set @ItemNameFieldsSql = @ItemNameFieldsSql + @ItemNameSql
'
-- 2、使用 exec sp_executesql 执行动态Sql
-- 2.1、定义动态Sql内部参数（输出参数） @ItemNameFieldsSql（与动态Sql内部的参数名 @ItemNameFieldsSql 必须一致）
-- 2.2、定义外部变量 @ResultSelectDynamicSqlOutput，用于接收返回值（动态Sql的输出值）
declare @ItemNameDynamiFieldsSqlOutput nvarchar(1000)
exec sp_executesql @FinalExecSql, N'@ItemNameFieldsSql nvarchar(1000) output', @ItemNameDynamiFieldsSqlOutput output
-- 3、通过动态Sql的输出值获取【构建动态列：行转列生成动态项目名称】Sql
set @ItemNameDynamiFieldsSql = @ItemNameDynamiFieldsSqlOutput
set nocount off
end
go
扫一扫
2654
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
