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 ' + QUOTENAME(@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

Answer 2

You need to use Dynamic SQL.

declare @ColName varchar(128)

declare @sql varchar(4000)

Set @ColName='Code';

select @sql = 'SELECT '+@ColName+'
  FROM [VoucherType]
 WHERE [DeletedBy] IS NULL
 AND [AutoID] = 1'
  exec sp_executesql @sql
go

This post might be helpful

Accessing a table from a name in a variable

SQL: Select dynamic column name based on variable

Regards