Use Variable as SQL column Name in query - Developers Anonymous
Friday, February 03, 2006 6:38 AM Trivium DawnWalker

Use Variable as SQL column Name in query

Hey guys,

Is it poosible to use a variable as a column name in SQL?

EXAMPLE:

DECLARE @TEST VARCHAR(100)

SET @TEST = 'NAME'

SELECT @TEST
FROM TABLE1

Quiet obviously I'm getting the value of @test back eachtime. I'm 99% sure its possible but my googling so far hasn't turned up anything.
Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!
Filed under:

Comments

# re: Use Variable as SQL column Name in query

Friday, February 03, 2006 9:05 AM by Trivium DawnWalker

I declared another variable. I used it as my string variable.

DECLARE @SQLQUERY VARCHAR(1000)

SET @SQLQUERY = N'SELECT DISTINCT (' + @COLUMNNAME + ') FROM TABLE1 WHERE ' + @COLUMNNAME + ' > '''

then

EXEC (@SQLQUERY)

# re: Use Variable as SQL column Name in query

Friday, February 03, 2006 3:06 PM by codingsanity

One problem, your solution leaves you open to a SQL injection attack (see http://dotnet.org.za/codingsanity/archive/2005/09/28/44998.aspx).

If you use the QUOTENAME function however you'll be fine:
SET @SQLQUERY = N'SELECT DISTINCT (' + QUOTENAME(@COLUMNNAME) + ') FROM TABLE1'

# re: Use Variable as SQL column Name in query

Friday, February 03, 2006 4:27 PM by Trivium DawnWalker

I never spotted that. Thanks!

# re: Use Variable as SQL column Name in query

Monday, October 22, 2007 1:19 AM by David Jones

How would this work with an Update statement?

SET @SQLQUERY = 'UPDATE @varTable SET ' + QUOTENAME(@F1) + ' = ' + LTRIM(@vTrans) + ','

+ QUOTENAME(@F2) +  '= "' + RTRIM(@vType) + '",'

+ QUOTENAME(@F3) + '="' + RTRIM(@vType2) + '",'

+ QUOTENAME(@LegalEntityAmountFName) + '=' + CAST(@Amount AS DECIMAL(18,5)) + '

WHERE Type =' + @vType

EXEC(@SQLQUERY)

Leave a Comment

(required) 
(required) 
(optional)
(required) 

Enter the numbers above: