how can I use a sproc @param as column alias?

Question

how can I use a sproc @param as column alias?

sqlguy-736318

Hall of Fame

Points: 3859
More actions
September 29, 2016 at 11:22 am

#330143

How can use a sproc param as a column alias? For example:
SELECT '12345' AS @MySprocParam
I tried the following sql but it doesn't work:
DECLARE @MySprocParam VARCHAR(50) = 'TestAlias'
SELECT 'ASDF' AS @MyProcParam

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183701 More actions · Answer 1

That's not possible without dynamic SQL. That's a bad idea, though.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ScottPletcher SSC Guru Points: 101046 More actions · Answer 2

Sample dynamic SQL:

DECLARE @MySprocParam VARCHAR(50) = 'TestAlias'

EXEC('SELECT ''ASDF'' AS [' + @MySprocParam + ']')

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

ScottPletcher (9/29/2016)
Sample dynamic SQL:
DECLARE @MySprocParam VARCHAR(50) = 'TestAlias'
EXEC('SELECT ''ASDF'' AS [' + @MySprocParam + ']')

This works until you have a [ or ] or some other funky characters in your string. This is easy enough to overcome using quotename. It does require another variable though because you can't have a function call inside a function call.

DECLARE @MySprocParam VARCHAR(50) = '[TestAlias]'

declare @sql nvarchar(Max) = 'SELECT ''ASDF'' AS ' + quotename(@MySprocParam)

EXEC(@sql)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/