CODEWITHSUNDEEP
sqlsql-server-2008
Pr0no
Pr0no

Reputation: 4109

Declare variable and use in query

Please consider the following

declare @MyField varchar(255);
set @MyField = 'MyDatabaseField';

select distinct Table.@MyField
from Table

This results in the error Incorrect syntax near @MyField. Then I tried:

select distinct Table.['+@MyField+']
from Table

However, this results in an Incorrect column name error.

How do I correctly use the @MyField in this query? I'm on SQL Server 2008.

Upvotes: 2

Views: 65

Answers (2)

Szymon
Szymon

Reputation: 43023

You should use dynamic SQL to achieve that. You can use sp_executesql stored proc to do that. Please not that I changed your variable declaration to **N**VARCHAR.

declare @MyField nvarchar(255)
set @MyField = N'MyDatabaseField'

declare @sql nvarchar(max) = N'select distinct ' + @MyField + N' from TableName'

exec sp_executesql @sql

Upvotes: 2

TechDo
TechDo

Reputation: 18659

Please try executing by building a string.

declare @MyField varchar(255);
set @MyField = 'MyDatabaseField';

exec ('select distinct Table.'+@MyField+' from Table')

Refer sp_executesql (Transact-SQL), Using sp_executesql

Upvotes: 2

Related Questions