select from column whose name is in a local var and set local var with value

Question

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:

declare @theID as int, @theIDColumnName as varchar(255), @theSQL as varchar(8000)
@theIDColumnName = 'MyPrimaryKey'  -- really this is set elsewhere

set @theSQL = 'Select @theID = i.'+@theIDColumnName+' from inserted i '
execute( @theSQL )

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!

Answer 1

0

yes as you've commented sp_executesql can be used for this

EXECUTE sp_executesql 
      N'select @theID = @theIDColumnName from inserted i',
      N'@theIDColumnName varchar(255)',
      @theIDColumnName = 'MyPrimaryKey';

answered Apr 14 '10 at 03:59 PM

Kev Riley ♦♦
46k ● 38 ● 43 ● 69

My head hurts. Kev, many thanks for this, but I tried it and I got the identical error message.

ALSO, I'm concerned that since this is in a trigger, and I'm accessing the inserted table in the trigger, will it go out of scope in the sp_executesql context, and not work, either?

Then do I have to send the inserted table in as a separately specified input parm? CAN that even be done?

And don't I have to do something to specify that @theID is an OUTPUT parm?

Apr 14 '10 at 05:48 PM ILA

OK, I think I've got theID sent in properly, but I'm getting the Msg 208, Level 16, State 1, Line 1 Invalid object name 'inserted'. error message. I'm not sure protocol, but perhaps I should post a new question regarding how to use sp_executesql with the inserted table?

Apr 14 '10 at 05:59 PM ILA

the error is due to the fact that you haven't declared the variable @theID - why are you trying to set this in a trigger?

Apr 14 '10 at 05:59 PM Kev Riley ♦♦

yes you might be better including the whole trigger sql as a separate question

Apr 14 '10 at 06:00 PM Kev Riley ♦♦

(comments are locked)

Answer 2

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.

alter trigger testrigger
on nametbl
after insert
as

declare @theId int
declare @theIDColumnName nvarchar(255)
declare @sql nvarchar(4000)

set @theIDColumnName = 'MyPrimaryKey'  

select * 
into #temptable
from inserted

select @sql = N'select @theID = ' + @theIDColumnName + ' from #temptable i'

EXECUTE sp_executesql @sql,      
    N'@theID int OUTPUT',  
    @theId =@theId;

select @theId

select from column whose name is in a local var and set local var with value

Follow this question

Related Questions