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

Answer 3

You cannot use a variable like that in a SELECT statement.

You will need to create dynamic TSQL.

You don't specify your RDBMS, but in SQL Server you would use sp_executesql (preferably) or EXEC

Declare @var1 varchar(100)
Declare @sql varchar(1000)

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

EXEC sp_executesql @sql

Be sure to read: The Curse and Blessings of Dynamic SQL

Answer 4

Please try with below code:

DECLARE @var1 VARCHAR(50)

SET @var1 = '[Code]'

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