将动态sql的结果分配给变量 [英] Assign result of dynamic sql to variable
问题描述
我正在执行动态 SQL 以将表中的所有列转换为字符串
I'm doing dynamic SQL to convert all columns in a table a string
毕竟我是这样做的
EXEC(@template);
其中@template 是动态生成的查询,所以:
where @template is the dynamic generated query so:
col1 col2 col3
---------------
1 7 13
2 8 14
3 9 15
4 10 16
5 11 17
6 12 18
(这个结果:1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18)
(this results: 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18)
如何将结果字符串分配给变量
How do I assign to a variable the resulting string
类似的东西?
DECLARE @result AS varchar(max);
SET @result = EXEC(@template);
推荐答案
您可以使用带有输出参数的 sp_executesql.
You can use sp_executesql with output parameter.
declare @S nvarchar(max) = 'select @x = 1'
declare @xx int
set @xx = 0
exec sp_executesql @S, N'@x int out', @xx out
select @xx
结果:
(No column name)
1
编辑
在我的示例中,@S
代替了你的 @template
.如您所见,我为 @x
分配了一个值,因此您需要修改 @template
以便它在内部将逗号分隔的字符串分配给您在第二个参数中定义的变量sp_executesql
.在我的示例 N'@x int out'
中.您可能需要 varchar(max)
输出参数.类似 N'@Result varchar(max) out'
In my sample @S
is instead of your @template
. As you can see I assign a value to @x
so you need to modify @template
so it internally assigns the comma separated string to the variable you define in your second argument to sp_executesql
. In my sample N'@x int out'
. You probably want a varchar(max)
output parameter. Something like N'@Result varchar(max) out'
这是从 master..spt_values 构建逗号分隔字符串的另一个示例
Here is another example building a comma separated string from master..spt_values
declare @template nvarchar(max)
set @template =
'select @Result += cast(number as varchar(10))+'',''
from master..spt_values
where type = ''P''
'
declare @CommaString varchar(max)
set @CommaString = ''
exec sp_executesql @template, N'@Result varchar(max) out', @CommaString out
select @CommaString
这篇关于将动态sql的结果分配给变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!