I want to select a value into a local variable from a column whose name is also in a local variable.
I set this up:
That seems to set @theSQL string correctly, but when I execute it, @theID is an unknown variable, so I get an error:
Msg 137, Level 15, State 1, Line 1 Must declare the scalar variable "@theID".
I realize the local var must be out of scope in the context of the exec, but I'm stumped as to how to solve this. (This is SS 2005, but might need to run on 2000) I've tried a few ideas, but haven't figured it out.
Many thanks to anyone who can help!
asked Apr 14 '10 at 03:42 PM in Default
yes as you've commented sp_executesql can be used for this
answered Apr 14 '10 at 03:59 PM
Kev Riley ♦♦
Kev, I think I am misunderstanding your sample. I was not able to get anything like that to work when I started playing with it. The two big issues if I am understanding right is that the inserted table will be out of scope and that it will try to take @theIDColumnName as a value instead of using its value to determine the column name. Could I ask you to clarify a little if I am just misunderstanding?
I think this may be similar to what is needed, which does involve inserted the inserted.* into a temp table and pasting the value of @theIDColumnName into the sql string rather than passing it directly to sp_executesql, but that is unlikely to be a substantial issue in most use cases.
answered Apr 14 '10 at 10:54 PM