Variable in query

Question

Hi I have a little problem. I must exec query in that style. In the example, something like that

declare @name varchar(max)
set @name = 'ColumnID'
select @name from Account

that return a lot of 'ColumnID' but I will have a result column ColumnID in Account table

Answer 1

You'll be wanting to execute a dynamic SQL Statement:

exec('select ' + @name + ' from Account');

Be wary of over-liberal use of these, as they can come with pretty hefty baggage:

Advantages

• It gives flexibility and scalability
• It can reduce the number of lines of code written

Disadvantages

• It can become very complex and difficult to read. Think about quotes embedded in quotes, and other such things.
• It can have a detrimental effect on code stability. Some Dynamic SQL errors will not be known until run time. (An example of this is where you reference a non-existent table)
• Dynamic SQL code is harder to test than the equivalent static SQL. It may also be impossible to test for every possible circumstance that your Dynamic SQL will encounter, thus introducing inherent risk.
• It will be more difficult to conduct an effective impact analysis on Dynamic SQL in your code-base.
• SQL injection and misuse - Dynamic SQL is more prone to misuse, and is invariably less safe than static SQL
• The queries code within Dynamic SQL is not subject to a query plan, and as such optimisations may be missed. As such, it can be slower than the equivalent static SQL As the SQL query is not known until runtime, it can be harder to performance-tune SQL
• Dynamic code (for example, determining the indexes that might be required on a table)

Answer 2

Try this:

declare @name varchar(max);
set @name = 'ColumnID';

exec('select ' + @name + ' from Account');

Answer 3

try using sp_executesql stored procedure

declare @name nvarchar(20);
set @name = 'ColumnID';
declare @sql nvarchar(max)
set @sql='select ' + @name + ' from Account'
exec sp_executesql  @sql