Select with variable assignment

Question

Select with variable assignment

Hairy Hat Man

SSC Enthusiast

Points: 148
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.

lionfan91 SSCrazy Eights Points: 8794 More actions · Answer 2

I haven't tried anything like this, but if there aren't many possible values for @mycolumnname, couldn't you use a CASE? Something like:

SELECT CASE @mycolumnname

WHEN 'colA' THEN colA

WHEN 'colB' THEN colB

etc

END

FROM tablename WHERE key = uniquevalue

just a thought...

Hairy Hat Man SSC Enthusiast Points: 148 More actions · Answer 3

Thanks for your help folks.

Unfortunately there are many possible values for @mycolumnname.

I eventually came up with two more workable, though inefficient, solutions:

1. Write the value of @mycolumnname to a ##temptable and then 'select' it back out again.

2. Build a string to declare a cursor containing the value of @mycolumnname and the exec(@declarecursorstring).

cmore SSCrazy Points: 2452 More actions · Answer 4

Unless it's a very limited environment, I tend to avoid using global temp tables. Never have liked the way it can make people step on each other.

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 5

I use global temp tables but I always put in a unique qualifier (e.g. spid) in the name of the table to prevent one user tripping over another.

Jeremy