tsql - 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.