Dynamic field names

[delphidestructor]

Can anyone tell me why the value of @var2 can be used to execute a query but the value of @var1 cannot? Is there anyway to force sql server 2000 to recognize the value in @var1 instead of trying to read this as a column name?


declare @var1 varchar(50)
declare @var2 int
select @var1 from a_table where a_column = @var2

I know I can do an exec statement and build a query string but ultimately I want to work with the value returned and if I do this then I cannot get the value into a variable.


declare @var1 varchar(50)
declare @var2 int
declare @realvar real
select @realvar = @var1 from a_table where a_column = @var2

I want to pass in a column name or an equation that uses more than one field. Please do not reply if you are going to give me a lecture on dynamic sql. A no this cannot be done or yes it can and this is how, would be great. Thank you in advance. Mike.


Mike

 

[hneal98]

You don't want people to answer you unless they give what you want, but what you are asking is not clear. What are you putting in @var1 and @var2?

 

[SQLSister]

You can use the exec statment to insert your value(s) into a temptable from the first Select statment and then use that to get the variable assignment later on.

Questions about posting. See faq183-874

 

[vongrunt]

Why the value of @var2 cannot be used the same way? For example:

declare @var2 varchar(50); set @var2 = 'someColumnName'
select blah from a_table where a_colum= @var2

Obviously there is a difference between "most desired" and "most consistent" behavior. AFAIK short answer is "no" - not without exec/dynamic SQL.

 

[delphidestructor]

Say I want '[Nitrate ppm]/[Sample Wt]' from the table this time or maybe just '[Ammonium]' the next time. The possible ways I might need information or values from this table varies greatly. I want to pass in '[Nitrate ppm]/[Sample Wt]' or '[Ammonium ppm]' into @var1 and then query based on this and return the result to another variable "@realvar". SQLSister I tried your method but this may happen more than once before the sp is completed. Once the temp table is created, even if I drop it afetr I pull the value out at that time, I cannot create it again. I think that it cannot be done. It's a catch 22 with everything. I cannot do a select besed on criteria (a value) within a variable unless I build a sql statement and call an execute. Then I can only execute into a temp table one time.

Mike

 

[delphidestructor]

I am obviously am doing something that I should do a differnt way. I will look at taking a different approach.

Mike

 

[SQLSister]

HOw about you add a column to the temptable to specify which time round it is and then increment that in a loop and select in part based on that number later?

Or is there a way to get all the values in the temp table in one select rather than running multiple ones?

Questions about posting. See faq183-874