Declaring dynamic variable names

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Declaring dynamic variable names

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-04-25 : 14:09:13

Matt writes "Hi there,

In a sql script I have declared 52 variables - one for each week of the year.

I have a while.. loop which gets the data for each week of the year. What I want to be able to do is concatenate the @counter variable with 'week' as a variable name so I get week1 on the first loop, week2 on the second etc.

so..


declare @week1 int
declare @week2 int etc.

while loop..
select t.minprice, 'week'+ (convert(varchar(2), @counter)) as availabilityIndicator from testVillasMatrix t
where t.weekNum=@counter
and t.propertyid=@pid

now..


select @'week' + convert(varchar(2), @counter)) = minprice

but this fails.. am I trying to do something that is impossible? I was hoping to find a function called evaluate() that I could use to set the variable.

Any help would be gratefully received..

thanks in advance..

Matt"

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-25 : 14:47:59

You have declared 52 variables?
why not use a table variable and add 52 entries to it.

Or you seem to already have these values in a table - why do you need to get them into variables?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-25 : 15:14:01

Matt --

time to learn some SQL ... it's a very different language than VB or others you might be used to.

what are you trying to do exactly? Why do you need a while loop? in SQL you work with entire SETS of data at a time, not 1 piece at a time.

If you have a table of numbers from 1 to 52:

create table Numbers(n int)
declare @i int;
set @i = 0
while @i <53
begin
insert into Numbers (@i)
set @i = @i + 1
end

Look at what you can do:

select 'Week ' + convert(varchar(2), n)
from Numbers

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources