x

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!

more ▼

asked Apr 14, 2010 at 03:42 PM in Default

ILA gravatar image

ILA
11 2 2 2

Oh, sorry: Wait, I think this will do it: sp_executesql MSDN on http://msdn.microsoft.com/en-us/library/ms188001.aspx

Thanks anyway!
Apr 14, 2010 at 03:51 PM ILA
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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'; 
more ▼

answered Apr 14, 2010 at 03:59 PM

Kev Riley gravatar image

Kev Riley ♦♦
52.8k 47 49 76

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, 2010 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, 2010 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, 2010 at 05:59 PM Kev Riley ♦♦
yes you might be better including the whole trigger sql as a separate question
Apr 14, 2010 at 06:00 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Apr 14, 2010 at 10:54 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x713
x51
x27
x17

asked: Apr 14, 2010 at 03:42 PM

Seen: 2863 times

Last Updated: Apr 14, 2010 at 03:42 PM