Select with variable assignment

Question

Post reply

Select with variable assignment

Hairy Hat Man

SSC Enthusiast

Points: 138
More actions
November 20, 2003 at 3:04 am

#160917

I want to return the result of a select statement into a @local_variable.

Normally, this is straight forward:

declare @myresult varchar(20)

select @myresult = surname from tablename where key = uniquevalue

But how can I do this if the columnname I want to retrieve is already in a local variable. For example:

declare @myresult varchar(20)

declare @mycolumnname varchar(50)

set @mycolumnname = 'surname'

How can I return the value of the column contained in @mycolumnname into @myresult?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

mia Right there with Babe Points: 749 More actions · Answer 1

Given that particular example, I think the only way to do this is with dynamic SQL using sp_executesql which accepts as parameters the SQL string to execute, a string listing the parameters and the parameters themselves.

Using your example as a starting point and assuming that @keyvalue is an integer, you might do something like the following:



declare @myresult varchar(20)

declare @mycolumnname varchar(50)

set @mycolumnname = 'surname'



declare @keyvalue int 

set @keyvalue = 1



declare @selectString nvarchar(500)

set @selectString = 'select @myresult = ' + @mycolumnname + ' from TABLENAME where KEY = @keyvalue'



EXEC sp_executesql @selectString,

     N'@myresult varchar(20) output, @keyvalue int', @myresult output, @keyvalue

There is a good section on sp_executesql in SQL books online & someone has recently posted a link to the following interesting article on dynamic SQL on another thread:

http://www.algonet.se/~sommar/dynamic_sql.html

Hope this helps!

Cheers,
mia

Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.