SQL Server variable columns name?

Question

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

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 2

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