今天分享一个动态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'
EXEC 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的人数
四、EXEC vs sp_executesql 对比
五、常见错误及解决
错误1：忘记加OUTPUT关键字
EXEC sp_executesql @sql, N'@a INT', @a = @num
EXEC sp_executesql @sql, N'@a INT OUTPUT', @a = @num OUTPUT
错误2：SQL字符串类型不对
DECLARE @sql VARCHAR(4000)
DECLARE @sql NVARCHAR(4000)
错误3：变量名写错
EXEC sp_executesql @sql, N'@a INT OUTPUT', @a = @num OUTPUT
六、实战案例
案例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 平均值
七、注意事项
八、快速模板
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
总结
一句话：EXEC拿不到结果，用sp_executesql配合OUTPUT参数就能轻松搞定！
阅读原文：原文链接
该文章在 2026/4/15 18:26:39 编辑过
400 186 1886
