SQL: Select dynamic column name based on variable
Solution 1
EXEC ('SELECT ''value'' AS ' + @myDynamicColumn)
Solution 2
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
Solution 3
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.
Related videos on Youtube
dotNetkow
Passionate developer focused on .NET and hybrid mobile apps via Ionic, Cordova, and Capacitor. Use your web development skills to create mobile apps! Check out my Pluralsight course, PhoneGap Build Fundamentals.
Updated on July 09, 2022Comments
-
dotNetkow almost 2 years
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.
-
ZygD about 13 yearsWhy? This isn't how SQL should be used
-
nathan_jr about 13 yearsHow about changing the procedure to take in @ReportId and then within procedure emulate what you would do in view... ie, IF @ReportId = 1 then select 'value' as [MyDynamicColumn] else if @ReportId = 2 ... At least you dont have to take on the baggage of dynamic sql. For a small number of reports/column headers I would go this route.
-
ZygDDRY would be to alias this in the client and keep the SQL contract identical. It isn't a SQL problem. DRY would also mean using the same name anyway. Having 4 names for one attribute is confusing...
-
-
dotNetkow about 3 yearsHello 10 years in the future! You are correct, this is a creative solution to a problem I had long ago. It's in a different comment but: The query was ran as a data connection in Excel for reporting purposes. The Client wanted 4 reports with relatively the same data but with different column names, so I made a stored proc for reusability & to follow the DRY principle. Only ever ran internally, and the sprocs stripped from Excel before passed over to the client. cheers!