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
Add a comment
|
5 Answers
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.
1 Comment
HLGEM
And do not use dynamic SQL unless you read this first: sommarskog.se/dynamic_sql.html
Because the column names are resolved at compile time not at run time for the SQL statement.
Comments
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')
3 Comments
David M
The question was why it is not allowed, not how to work around it. I can't see that answering with "Because it is not allowed" is very helpful.
Oleks
@David M: yep, this is a workaround only. Your answer is helpful
Tomasz Gandor
Thanks for
exec - it is easier to remember than exec sp_executesql blah blah. I remember always getting errors with this SP, like "not permitted to execute this procedure".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'''
;
Comments
If there are not too many columns to chose, how about a CASE WHEN statement?
DECLARE @ColName VARCHAR(50) = 'Test1'
SELECT [Foo], [Bar],
CASE
WHEN @ColName = 'Test1' THEN [Test1]
WHEN @ColName = 'Test2' THEN [Test2]
WHEN @ColName = 'Test3' THEN [Test3]
WHEN @ColName = 'Test4' THEN [Test4]
ELSE [TestDefault]
END [TestResult]
FROM [TableName];
This avoids using any EXEC.
