SQL Server variable columns name?

Question

Hi,

I am wondering why I cannot use variable column name like that:

declare @a as varchar;
set @a='TEST'

select @a from x;

Thank you

Answer 1

Because the column names are resolved at compile time not at run time for the SQL statement.

Answer 2

use sp_executesql for this

Example
SET @SQLString = N'SELECT *
    FROM table1
    WHERE timet = @time and items in (@item)';


DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

SET @ParmDefinition = N'@time timestamp,
    @item varchar(max) ';

EXECUTE sp_executesql
    @SQLString
    ,@ParmDefinition
    ,@time = '2010-04-26 17:15:05.667'
    ,@item = '''Item1'',''Item2'',''Item3'',''Item4'''
    ;

Answer 3

You can't do it because SQL is compiled before it knows what the value of @a is (I'm assuming in reality you would want @a to be some parameter and not hard coded like in your example).

Instead you can do this:

declare @a as varchar; 
set @a='TEST' 

declare @sql nvarchar(max)
set @sql = 'select [' + replace(@a, '''', '''''') + '] from x'

exec sp_executesql @sql

But be careful, this is a security vulnerability (sql-injection attacks) so shouldn't be done if you can't trust or well clean @a.

Answer 4

Because it is not allowed.

Insted of this you could use dynamic sql query:

declare @a as varchar;
set @a='TEST'
exec ('select ' + @a + ' from x')