Dynamic column selection in SQL query

Question

I have a call to the name of the Code database field name, and I'm trying to select it using the variable name, as shown below:

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

SQL seems to interpret @var1 as a string, not a field in my database, how can I do it this way @var1 recognized as the name of the [Code] field instead of the string, possibly without any select or if.

Answer 1

Try the following:

 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 will need to compose a dynamic query and execute using sp_executesql

To add more about 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 cannot use such a variable in a SELECT statement.

You will need to create a dynamic TSQL.

You do not specify your RDBMSs, but in SQL Server you should 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: Curse and blessings of dynamic SQL

Answer 3

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 may be helpful.

Access a table from a name in a variable

SQL: select dynamic column name based on variable

Hi

Answer 4

Try using the code below:

DECLARE @ var1 VARCHAR (50)

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