CODEWITHSUNDEEP
sqlsql-serverstored-procedures
dotNetkow
dotNetkow

Reputation: 5313

SQL: Select dynamic column name based on variable

I have a Microsoft SQL stored procedure whose column name I want to set via a variable that is passed into it:

CREATE PROCEDURE [My_Procedure]
   @myDynamicColumn varchar(50)
AS BEGIN
   SELECT 'value' AS @myDynamicColumn
END

This does not work ("Incorrect syntax"). If I wrap the column name with [ ]:

SELECT 'value' AS [@myDynamicColumn]

The column name literally outputs as '@myDynamicColumn' instead of the actual value. Is there any way to do this? I've looked into dynamic SQL articles but nothing is quite what I'm asking for.

Upvotes: 28

Views: 75431

Answers (3)

Thom A
Thom A

Reputation: 96027

Both the upvoted answers are very dangerous here, both are wide open to injection attacks and should not be used.

When injecting dynamic object names you must ensure you properly quote your object names. SQL Server has a built in function for that, QUOTENAME. Thus what you should actually be doing is the following:

CREATE PROCEDURE [My_Procedure] @myDynamicColumn sysname
AS BEGIN
    DECLARE @SQL nvarchar(MAX) = N'SELECT ''value'' AS ' + QUOTENAME(@myDynamicColumn) + N';';
    EXEC sys.sp_executesql @SQL;
END

You'll note I also change the data type of the parameter to sysname, a synonym for nvarchar(128) NOT NULL, which is the data type SQL Server uses internally for object names.

Upvotes: 6

Josh
Josh

Reputation: 16567

You could build your query into a string and use exec

CREATE PROCEDURE [My_Procedure]
   @myDynamicColumn varchar(50)
AS BEGIN
   EXEC('SELECT ''value'' AS ' + @myDynamicColumn)
END

Upvotes: 22

Joe Stefanelli
Joe Stefanelli

Reputation: 135938

EXEC ('SELECT ''value'' AS ' + @myDynamicColumn)

Upvotes: 38

Related Questions