CODEWITHSUNDEEP
sqlt-sql
adamowski
adamowski

Reputation: 189

Variable in query

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

Upvotes: 1

Views: 154

Answers (3)

DeveloperX
DeveloperX

Reputation: 4683

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

Upvotes: 0

Mutation Person
Mutation Person

Reputation: 30498

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)

Upvotes: 3

Andrew Hare
Andrew Hare

Reputation: 351516

Try this:

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

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

Upvotes: 1

Related Questions