sql server - Dynamically choose column in SQL query

Question

I have a database field name call Code and I am trying to select it using a variable name like this below:

Declare @var1 = [Code]

(SELECT @var1
 FROM [VoucherType]
 WHERE [DeletedBy] IS NULL
 AND [AutoID] = 1)

Apparently, SQL will interpret @var1 as a string and not the field of my database, how can I do it in such a way @var1 is recognized as the field name [Code] instead of a string possibly without any select or if statements.

Answer 1

Try this:

DECLARE @var1 VARCHAR(20)
DECLARE @sql VARCHAR(255)

SET @var1 = 'Code'
SET @sql = 'select ' + @var1 + ' from [VoucherType] where [DeletedBy] is null and [AutoID] = 1'

EXEC sp_executesql @sql

You'll have to compose a dynamic query, and execute using sp_executesql

To add more on the 'dynamic' side of things, use stored procedures. See here for an example:

http://www.marten-online.com/database/execute-dynamic-sql-in-mssql.html

That is... if you are using Microsoft SQL SERVER