SQL:动态变量名 [英] SQL: Dynamic Variable Names
本文介绍了SQL:动态变量名的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我试图在存储过程中设置名称为动态的变量:
I am attempting to set variables whose names are dynamic in a stored procedure:
DECLARE @var01 varchar(50)
DECLARE @var02 varchar(50)
...
DECLARE @var30 varchar(50)
DECLARE @sql = varchar(max)
DECLARE @loopcnter INT
-- (Inside some loop where the loopcounter increments each iteration)
...
SET @sql = 'SET @var0'+CAST(@loopcntr AS Varchar)+'= '''+'somevalue'+''''
-- e.g.) SET @var01= 'somevale'
EXEC (@sql)
这不起作用,因为变量是在与动态 sql 不同的范围内声明的.
This doesn't work because the variables are declared in a different scope to that of the dynamic sql.
以这种方式动态设置变量的正确方法是什么?
What is the correct way to dynamically set variables in this manner?
推荐答案
好吧,它不漂亮,但你可以这样做:
Well, it is not pretty, but you can do:
if @loopcntr = 1
set var01 = 'somevalue'
else if @loopcntr = 2
set var02 = 'whatever'
else if . . .
这应该足够令人不快,您可能会想到替代方案.哦,这是一个很好的.定义一个表变量并为每个值添加行:
This should be sufficiently unpleasant that you might think of alternatives. Oh, here's a good one. Define a table variable and just add rows in for each value:
declare @vars table (
id int identity(1, 1),
loopcntr int,
value varchar(255)
);
. . .
-- inside the loop
insert into @vars(loopcntr, value)
select @loopcntr, 'whatever';
当你想得到一个变量时,你可以这样做:
When you want to get a variable, you can do:
declare @var varchar(255);
select @var = value from @vars where loopcntr = <the one I want>;
这篇关于SQL:动态变量名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文