CODEWITHSUNDEEP
sqlsql-server
Tomb_Raider_Legend
Tomb_Raider_Legend

Reputation: 461

SQL Server: how to set a column name as the value of a dynamic variable?

As the example below (maybe not a good example): 

DECLARE @var1 VARCHAR(50)
SET @var1 = (SELECT TOP(1) MiddleName FROM myTable)

SELECT Name, SSN AS @var1 
FROM myTable

What I am trying to do it set a value in a variable by using AS statement (in this case the first MiddleName value in the table myTable as the SSN column name). Any ideas on how to make this work?

Upvotes: 2

Views: 1128

Answers (3)

varun kumar dutta
varun kumar dutta

Reputation: 202

Try this:

    DECLARE @var1 VARCHAR(50)
  , @sql NVARCHAR(MAX)
SET @var1 = ( SELECT TOP ( 1 )
                        MiddleName
              FROM      myTable
            )

SET @sql = N'SELECT Name, SSN' + ' ' + @var1 + 'FROM myTable'

EXEC sys.sp_executesql @sql

NOTE:- Remember to have at least one space before @var1.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271161

If you really need to do this, use exec() or sp_executesql:

DECLARE @var1 VARCHAR(50);
SET @var1 = (SELECT TOP(1) MiddleName FROM myTable);

DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT Name, SSN AS [@var1] FROM myTable';
SET @sql = REPLACE(@sql, '@var1', @var1);

EXEC sp_executesql @sql;

Upvotes: 2

ydoow
ydoow

Reputation: 3006

You'll need dynamic SQL.

DECLARE @sqlText nvarchar(1000); 

DECLARE @var1 VARCHAR(50)
SET @var1 = (SELECT TOP(1) MiddleName FROM myTable)

SET @sqlText = N'SELECT Name, SSN AS ' + @var1 + ' FROM myTable'
Exec (@sqlText)

Upvotes: 3

Related Questions