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: 183698 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: 100950 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) A socialist is someone who will give you the shirt off *someone else's* back.

Sean Lange SSC Guru Points: 286573 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/