SQL: Select dynamic column name based on variable

Question

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.

Answer 1

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

Answer 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