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

Question

question

ILA asked Apr 14, '10

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!

sql dynamic variable execute

1

10 |1200

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

ILA commented · Apr 14, 2010 at 03:51 PM

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 ·

Answer 1 · 2010-04-14T15:59:56Z

Kev Riley answered Apr 14, '10

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

10 |1200

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

Kev Riley ♦♦ commented · Apr 14, 2010 at 05:59 PM

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 ·

ILA commented · Apr 14, 2010 at 05:48 PM

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 ·

ILA commented · Apr 14, 2010 at 05:59 PM

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 ·

Kev Riley ♦♦ commented · Apr 14, 2010 at 06:00 PM

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

0 ·

Answer 2 · 2010-04-14T22:54:32Z

TimothyAWiseman answered Apr 14, '10

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

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

question