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.

Coatee

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

Chlo

+ @var1 + should be + QUOTENAME(@var1) + to avoid problems with spaces and other possible special characters in column names. It also helps prevent possible injection issues. Also, If you're composing a comma separated list each individual column name should be passed through QUOTENAME. – Officiary

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

Auspicate

EXEC should be avoided in favor of sp_executesql because it is more efficient: reference – Acree

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

Dinnage

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 ')

Arabeila

EXEC should be avoided in favor of sp_executesql because it is more efficient: reference – Acree

Answer 5

12 Years, 8 Months too late!

For the cross apply used here, make sure to use it on a unique index (unless you need an array of objects)

-- INIT database
CREATE TABLE Product (
  ProductID INT IDENTITY(1, 1),
  Preferred_Data_Point INT,
  Data_Point_1 VARCHAR(10),
  Data_Point_2 VARCHAR(10),
  Data_Point_3 VARCHAR(10)
);

INSERT INTO Product(Data_Point_1, Data_Point_2, Data_Point_3) VALUES ('Hello', 'World', '*wave*');
INSERT INTO Product(Data_Point_1, Data_Point_2, Data_Point_3) VALUES ('Hello', 'World', '*wave*');
INSERT INTO Product(Data_Point_1, Data_Point_2, Data_Point_3) VALUES ('Hello', 'World', '*wave*');

-- Dynamic Selection Criteria (Define Column Name) (Useful for views)
SELECT 
    [Product].ProductID,
    JSON_VALUE(ProductJSON.JSON, CONCAT('$.Data_Point_', Product.ProductID)) AS [value]
FROM Product [Product] WITH (NOLOCK)
CROSS APPLY -- 1to1 Cross Apply Row w/ JSON equivalent
(
    SELECT (SELECT *
    FROM Product [jsonProduct] WITH (NOLOCK)
    WHERE jsonProduct.ProductID = Product.ProductID
    FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER) [JSON]
) [ProductJSON];

-- More Obvious
INSERT INTO Product(Data_Point_1, Data_Point_2, Data_Point_3, Preferred_Data_Point) VALUES ('Hello', 'World', '*wave*', 2);
INSERT INTO Product(Data_Point_1, Data_Point_2, Data_Point_3, Preferred_Data_Point) VALUES ('Hello', 'World', '*wave*', 3);
INSERT INTO Product(Data_Point_1, Data_Point_2, Data_Point_3, Preferred_Data_Point) VALUES ('Hello', 'World', '*wave*', 1);

-- Select based on rows other column values (same as before, but different)
SELECT 
    [Product].ProductID,
    JSON_VALUE(ProductJSON.JSON, CONCAT('$.Data_Point_', Product.Preferred_Data_Point)) AS [value]
FROM Product [Product] WITH (NOLOCK)
CROSS APPLY -- 1to1 Cross Apply Row w/ JSON equivalent
(
    SELECT (SELECT *
    FROM Product [jsonProduct] WITH (NOLOCK)
    WHERE jsonProduct.ProductID = Product.ProductID
    FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER) [JSON]
) [ProductJSON];

Preview here: SQLFiddle

Hufford