SQL: Dynamic Variable Names

Question

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)

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?

why do you need the names to be dynamic? what is the larger problem you're trying to solve? — gloomy.penguin
– gloomy.penguin, Commented Nov 4, 2013 at 16:47
What are you trying to achieve ? Indeed, dynamic sql has its own scope — jazzytomato
– jazzytomato, Commented Nov 4, 2013 at 16:47
I don't think it's possible and I don't think it's a good idea to try to create such variable. Please give more detail what are you trying to achieve ?? — Kuldeep
– Kuldeep, Commented Nov 4, 2013 at 17:00
Loops in SQL are evil and inefficient. Can you show us what you're trying to accomplish in your dynamic SQL? Maybe someone can offer an alternative. — Andrew
– Andrew, Commented Nov 4, 2013 at 17:05

Gordon Linoff · Accepted Answer · 2013-11-04 17:14:59Z

8

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>;

answered Nov 4, 2013 at 17:14

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gloomy.penguin Over a year ago

+1 "This should be sufficiently unpleasant that you might think of alternatives."

Andriy M · Accepted Answer · 2013-11-05 07:12:52Z

Since you are saying in comments that you are assigning the set of variables merely to insert the assigned values into a table, you could use an approach avoiding the variables altogether. You could insert all the values into a temporary table or table variable, providing them with numeric indices, then pivot the column of values and insert the resulting row into the target table. So, something like this:

DECLARE @values TABLE (Ind int IDENTITY(1,1), Value varchar(50));

INSERT INTO @values
SELECT ...
;

INSERT INTO TargetTable (Col1, Col2, ..., Col30)
SELECT [1], [2], ..., [30]
FROM @values
PIVOT (
  MAX(Value) FOR Ind IN ([1], [2], ..., [30])
) AS p
;

I even suspect there might also be a possibility to do everything in such a way as to avoid the cursor you are mentioning in comments.

Bogdan Bogdanov · Accepted Answer · 2013-11-06 16:53:46Z

0

You can also use stored procedure sp_executesql which allows passing parameters. Although I recommend to use additional table to store variables.

edited Nov 6, 2013 at 16:53

answered Nov 4, 2013 at 19:20

Bogdan Bogdanov

1,7232 gold badges21 silver badges31 bronze badges

Collectives™ on Stack Overflow

SQL: Dynamic Variable Names

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related