use variables to assign result set column names

Question

use variables to assign result set column names

pbarr

SSC Rookie

Points: 29
More actions
February 14, 2002 at 3:47 pm

#77947

I have a sp that returns a column for each month of the most recent quarter. Is there a way to return the month name as the column name?

Viewing 5 posts - 1 through 5 (of 5 total)

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

You could do it dynamically by building a the SQL string in a variable object concatinating the names in the way you want and then executing the variable.

Ex.

DECLARE @SQLStr AS VARCHAR(500)

SET @SQLStr = 'SELECT col1 AS ' + WHATEVERTOGETTHEMONTHNAME + ' FROM tblX'

EXE(@SQLStr)

However, you break the ownership chain and may have to give SELECT rights to the underlying table itself. Let me know if you need a better example.

pbarr SSC Rookie Points: 29 More actions · Answer 2

cool,

I can get it to work using

execute sp_executesql @stmt

is that what you mean?

is that the most efficient way?

Steve Jones - SSC Editor SSC Guru Points: 738347 More actions · Answer 3

Steve Jones - SSC Editor

SSC Guru

Points: 738347

February 15, 2002 at 3:42 pm

#423977

just exec( @stmt)

Steve Jones

steve@dkranch.net

Antares686 SSC Guru Points: 125444 More actions · Answer 4

And you do have to have the ()'s around your variable.

use variables to assign result set column names

Cookies on SQLServerCentral