Menu
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.
315
Solution 1 The only way to do that is use build your command into a string, and use EXEC to run the result: table and column name parsing is conducted early in the SQL command execution process and have been replaced before any of the actual query is executed.
They allow to encapsulate the SQL query and reuse result recursively as a data source in the main query or in another CTE within one statement. In this article I'll illustrate how to use CTEs using an example of dynamic query which counts records from one table joined with second table used to limit the result set.
EXEC ('SELECT ''value'' AS ' + @myDynamicColumn) 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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
