question

ILA avatar image
ILA asked

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

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!

sqldynamicvariableexecute
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ILA avatar image ILA commented ·
Oh, sorry: Wait, I think this will do it: sp_executesql MSDN on http://msdn.microsoft.com/en-us/library/ms188001.aspx Thanks anyway!
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered

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';
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
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?
1 Like 1 ·
ILA avatar image ILA commented ·
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?
0 Likes 0 ·
ILA avatar image ILA commented ·
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?
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
yes you might be better including the whole trigger sql as a separate question
0 Likes 0 ·
TimothyAWiseman avatar image
TimothyAWiseman answered

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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.