Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Dynamic variable names

Status
Not open for further replies.

robertkjr3d

Programmer
Dec 19, 2003
36
US
Ok here is the simple version of what my problem is:
declare @Field int
declare @Field2 int
declare @sql varchar(150)
set @Field = 2
set @Field2 = 2000
set @sql = 'select @Field' + Convert(varchar, @Field) + ' [Value]'
exec(@sql)

I get the error must declare the variable @Field2

It doesn't remember.

Why do I need this really is because of this situation:
(All of these variables are declared)

set @loop = 1
set @visits = 0
set @Field = 11
--Collect visits
while @loop = 1 and @Field <= 68 begin
set @loop = 0
set @command = 'if (@Field' + Convert(varchar, @Field) + ' is not null and @Field' + Convert(varchar, @Field + 1) + ' = ''T'' and @Field' + Convert(varchar, @Field + 2) + ' is not null) begin set @loop = 1 set @visits = @visits + 1 end'
exec(@command)
set @Field = @Field + 3
end

However I get the error, @loop, @visits, @Field not declared.

Is there anyway to handle a @ variable without knowing the complete name of it? Because I have variables passed to the procedure in the form of @Field1, @Field2, @Field3... and so on
 
You must use the variables outside of the quote marks.
Code:
...
set @sql = 'select ' + @Field + Convert(varchar, @Field) + ' [Value]'
...


This produces the statement
Code:
select 22 [Value]

What that might do, I have no idea.


The error message is due to the issue of scope. Anything in the sql string has its own scope. Nothing within the string can refer to things outside the string.


Does that help?



 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top