CODEWITHSUNDEEP
sqlsql-server
w0051977
w0051977

Reputation: 15787

Column heading contained in TSQL variable

Please see the DDL below:

CREATE TABLE TestTable (id int identity, name varchar(30))
INSERT INTO TestTable (Name) VALUES ('Ian')

declare @Test As varchar(100)
set @Test = 'Name'
SELECT @Test FROM TestTable

The output from the SELECT is 'Name'. I want the output to be: 'Ian'. How?

Upvotes: 0

Views: 1783

Answers (3)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

You can't use a variable to tell SQL Server what column, table, database etc. you want to use. You need to enclose this type of code in dynamic SQL.

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT ' + QUOTENAME(@Test) + ' FROM dbo.TestTable;';
EXEC sp_executesql @sql;

Here's why I prefer sp_executesql instead of EXEC() as a standard best practice, and here's why you should always use the schema prefix (e.g. dbo.) when referencing objects. QUOTENAME() can help protect you from SQL injection in this case, since I don't know where the value for @Test ultimately comes from.

Upvotes: 3

Sadique
Sadique

Reputation: 22823

Generate a string dynamically and use exec

EXEC ('SELECT ' + @Test + ' FROM TestTable')

Upvotes: 0

D Stanley
D Stanley

Reputation: 152511

You can use EXEC to execute a fabricated SQL string:

declare @Test As varchar(100)
set @Test = 'Name'
EXEC ('SELECT ' + @Test + ' FROM TestTable')

The standard warning for these answers is be certain that you can control what gets put in the SQL statement or use restrictive rights (e.g. read-only) to execute them, otherwise you could get something like this:

declare @Test As varchar(100)
set @Test = '1 ; DROP TABLE TestTable; --'
EXEC ('SELECT ' + @Test + ' FROM TestTable')

Upvotes: 0

Related Questions