动态SQL结果存入变量：sp_executesql的进阶用法

今天分享一个动态SQL中的常见问题——如何将EXEC执行的结果存到变量里。

一、问题场景

动态SQL执行后，结果只显示在结果窗口，没法直接赋值给变量：

DECLARE @sql NVARCHAR(4000)SET @sql = 'SELECT COUNT(*) FROM users'
EXEC(@sql)  -- 结果看得见，但拿不到

二、解决方案：sp_executesql + OUTPUT

方法一：用sp_executesql带output参数

DECLARE @num INTDECLARE @sql NVARCHAR(4000)
SET @sql = 'SELECT @a = COUNT(*) FROM users'
-- 执行动态SQL，把结果赋给@numEXEC sp_executesql @sql, N'@a INT OUTPUT', @a = @num OUTPUT
SELECT @num AS 结果

方法二：多个输出参数

DECLARE @count INT, @avgAge INT, @maxAge INTDECLARE @sql NVARCHAR(4000)
SET @sql = '    SELECT         @a = COUNT(*),        @b = AVG(age),        @c = MAX(age)    FROM users'
EXEC sp_executesql @sql,     N'@a INT OUTPUT, @b INT OUTPUT, @c INT OUTPUT',    @a = @count OUTPUT,    @b = @avgAge OUTPUT,    @c = @maxAge OUTPUT
SELECT @count AS 总数, @avgAge AS 平均年龄, @maxAge AS 最大年龄

三、完整示例

-- 创建测试表CREATE TABLE #users (id INT, name VARCHAR(50), age INT)INSERT INTO #users VALUES (1, '张三', 25), (2, '李四', 30), (3, '王五', 28)
-- 动态查询：统计年龄大于某个值的记录数DECLARE @minAge INT = 26DECLARE @count INTDECLARE @sql NVARCHAR(4000)
SET @sql = 'SELECT @a = COUNT(*) FROM #users WHERE age ＞ @age'EXEC sp_executesql @sql,     N'@age INT, @a INT OUTPUT',    @age = @minAge,    @a = @count OUTPUT
SELECT @count AS 年龄大于26的人数  -- 结果：2

四、EXEC vs sp_executesql 对比

特性	EXEC	sp_executesql
获取输出值	❌ 不支持	✅ 支持OUTPUT
参数化查询	❌ 需拼接字符串	✅ 支持参数
SQL注入风险	较高	较低
执行计划重用	❌ 不重用	✅ 可重用
适用场景	简单动态SQL	需要输出或参数化

五、常见错误及解决

错误1：忘记加OUTPUT关键字

-- ❌ 错误EXEC sp_executesql @sql, N'@a INT', @a = @num
-- ✅ 正确EXEC sp_executesql @sql, N'@a INT OUTPUT', @a = @num OUTPUT

错误2：SQL字符串类型不对

-- ❌ 错误：用VARCHARDECLARE @sql VARCHAR(4000)
-- ✅ 正确：必须用NVARCHARDECLARE @sql NVARCHAR(4000)

错误3：变量名写错

-- 注意：@a是SQL内部的变量名，@num是外部的变量名EXEC sp_executesql @sql, N'@a INT OUTPUT', @a = @num OUTPUT--                           ↑                    ↑--                     SQL内部变量名        外部接收变量

六、实战案例

案例1：动态表名+统计数量

CREATE PROCEDURE usp_GetTableCount    @tableName NVARCHAR(128)ASBEGIN    DECLARE @count INT    DECLARE @sql NVARCHAR(4000)
    SET @sql = 'SELECT @a = COUNT(*) FROM ' + QUOTENAME(@tableName)    EXEC sp_executesql @sql, N'@a INT OUTPUT', @a = @count OUTPUT
    SELECT @count AS 记录数END
-- 调用EXEC usp_GetTableCount 'users'

案例2：动态字段求和

DECLARE @fieldName NVARCHAR(50) = 'salary'DECLARE @total DECIMAL(18,2)DECLARE @sql NVARCHAR(4000)
SET @sql = 'SELECT @a = SUM(' + @fieldName + ') FROM employees'EXEC sp_executesql @sql, N'@a DECIMAL(18,2) OUTPUT', @a = @total OUTPUT
SELECT @total AS 工资总和

案例3：返回值存入多个变量

DECLARE @minVal INT, @maxVal INT, @avgVal INTDECLARE @tableName NVARCHAR(50) = 'products'DECLARE @fieldName NVARCHAR(50) = 'price'DECLARE @sql NVARCHAR(4000)
SET @sql = '    SELECT         @a = MIN(' + @fieldName + '),        @b = MAX(' + @fieldName + '),        @c = AVG(' + @fieldName + ')    FROM ' + QUOTENAME(@tableName)
EXEC sp_executesql @sql,     N'@a INT OUTPUT, @b INT OUTPUT, @c INT OUTPUT',    @a = @minVal OUTPUT,    @b = @maxVal OUTPUT,    @c = @avgVal OUTPUT
SELECT @minVal AS 最小值, @maxVal AS 最大值, @avgVal AS 平均值

七、注意事项

要点	说明
字符串类型	必须用`NVARCHAR`
OUTPUT位置	定义和调用时都要写
参数顺序	定义顺序要和赋值顺序一致
注入风险	动态表名/字段名用`QUOTENAME`包裹

八、快速模板

-- 通用模板：单值返回DECLARE @result INTDECLARE @sql NVARCHAR(4000)
SET @sql = 'SELECT @a = COUNT(*) FROM 你的表名 WHERE 条件'EXEC sp_executesql @sql, N'@a INT OUTPUT', @a = @result OUTPUT
SELECT @result
-- 通用模板：多值返回DECLARE @r1 INT, @r2 VARCHAR(50)DECLARE @sql NVARCHAR(4000)
SET @sql = 'SELECT @a = 字段1, @b = 字段2 FROM 你的表名 WHERE 条件'EXEC sp_executesql @sql,     N'@a INT OUTPUT, @b VARCHAR(50) OUTPUT',    @a = @r1 OUTPUT,    @b = @r2 OUTPUT
SELECT @r1, @r2

总结

需求	方案
动态SQL结果存入变量	`sp_executesql` + `OUTPUT`
单个返回值	1个OUTPUT参数
多个返回值	多个OUTPUT参数
动态表名/字段名	用`QUOTENAME`防注入

一句话：EXEC拿不到结果，用sp_executesql配合OUTPUT参数就能轻松搞定！

阅读原文：原文链接