28

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.

Improve this question
6
  • 1
    Why? This isn't how SQL should be used Commented Apr 12, 2011 at 15:51
  • @gbn: right on. @dotNewkow: Im sure this is just a contrived example to illustrate your issue, but gbn is correct: this is complex because its wrong. If you need to alias a return from the stored procedure then just do the aliasing in the calling code, where you obviously already know the value of @myDynamicColumn. If you post more details about your problem perhaps we can offer more than dynamic sql. Commented Apr 12, 2011 at 16:01
  • Good question. Yes, I understand the dangers of dynamic SQL. @Nathan Skerl, you're correct, normally you'd want to set this via the calling code. However, I'm running this query as a data connection in Excel for reporting purposes. The Client wants 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. If this was a view, I could do: "SELECT [column] AS [My Dynamic column name] FROM [My View]" but since it's a stored proc I can only do "EXEC My_Procedure 'My Column Name'". Commented Apr 12, 2011 at 16:10
  • 1
    DRY 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... Commented Apr 12, 2011 at 16:13
  • 1
    How 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. Commented Apr 12, 2011 at 16:26

3 Answers 3

Reset to default
38 
EXEC ('SELECT ''value'' AS ' + @myDynamicColumn)
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks, you answered 45 seconds after the other guy, so have an upvote!
@dotNetkow: Actually, I was 9 seconds ahead of "the other guy", 15:49:30 vs. 15:49:39.
Always a bridesmaid, never a bride.
This answer is dangerous at best; it's wide open to injection attacks.
22

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
Improve this answer

1 Comment

This answer is dangerous at best; it's wide open to injection attacks.
6

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.