SQL Server variable columns name?
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
Solution 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.
Solution 2:
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')
Solution 3:
Because the column names are resolved at compile time not at run time for the SQL statement.
Solution 4:
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'''
;