Parameter as Column Name

Question

Post reply

Parameter as Column Name

ganny

Mr or Mrs. 500

Points: 531
More actions
July 31, 2008 at 11:27 pm

#103545

Hi,
I would like to use the parameter as my column name in my report. Please find the below example what am expecting.
1. Created a precedure with the parameters:
CREATE PROCEDURE ESLsp_ServiceOrder
@Month1 varchar(10),
@Month2 varchar(10),
@Month3 varchar(10)
2. passed with the below mentioned values into the parameters:
ESLsp_ServiceOrder 'May','Jun','Jul'
3. i have written the select query as below to show parameter values as my column names:
SELECT NAME, ABC AS @Month1, XYZ AS @Month2, MNO AS @Month3 FROM TEMPTABLE.
Output should show the column names as below:
NAME MAY JUN JUL
--------------------
a 10 20 30
b 20 10 10
But i am getting an error while execute the above query as Incorrect Syntax near @Month1,@Month2,@Month3..
Can anyone please assist me the correct syntax to show the above output.

Viewing 10 posts - 1 through 9 (of 9 total)

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

varaprasad_pv SSC Rookie Points: 32 More actions · Answer 1

varaprasad_pv

SSC Rookie

Points: 32

August 1, 2008 at 3:41 am

#851718

Use the dynamic sql to get the required output.

OptimusPrime Ten Centuries Points: 1379 More actions · Answer 2

this should work:

declare @sql varchar(8000)

set @sql = ' SELECT NAME, ABC AS ' + @Month1 + ', XYZ AS ' + @Month2 + ', MNO AS ' + @Month3 + ' FROM TEMPTABLE '

exec (@sql)

Let me know how it goes.

koosubscriptions Old Hand Points: 335 More actions · Answer 3

Is there any performance cost compare with if the query already exists?

Thank you.

Jeff Moden SSC Guru Points: 1004433 More actions · Answer 4

koosubscriptions (8/3/2008)
Is there any performance cost compare with if the query already exists?
Thank you.

Probably not... it would probably recompile even if it were hard coded just because of data changes since the last time you used it... and, on a busy system, the life expectancy of an execution plan can be very low.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

koosubscriptions Old Hand Points: 335 More actions · Answer 5

koosubscriptions

Old Hand

Points: 335

August 3, 2008 at 12:56 pm

#852465

Oh. Thanks then. I'll give it a try.

Jeffrey Williams SSC Guru Points: 90313 More actions · Answer 6

I probably wouldn't use a dynamic SQL approach, because that is going to be much harder to work with on the report side anyways. How are you going to tie your report to columns that will change every month/quarter/year that you run it?

Instead, create the procedure to always return the data in the columns 'Month1', 'Month2' and 'Month3'. The columns in the report will be tied to those specific columns. Now, in the report you can modify the labels for each fixed column based upon the specific date range selected.

At least, that is the way I would do something like this.

The other approach - which may even be better (you'll have to test) would be to return the data vertically and perform the crosstab (matrix) in the report. So, instead of the three month columns you would have a single month column with the values 'Jan', 'Feb', 'Mar' and the value(s) for each. Then, you could let the report crosstab that as needed.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004433 More actions · Answer 7

Jeffrey Williams (8/3/2008)
I probably wouldn't use a dynamic SQL approach, because that is going to be much harder to work with on the report side anyways. How are you going to tie your report to columns that will change every month/quarter/year that you run it?
Instead, create the procedure to always return the data in the columns 'Month1', 'Month2' and 'Month3'. The columns in the report will be tied to those specific columns. Now, in the report you can modify the labels for each fixed column based upon the specific date range selected.
At least, that is the way I would do something like this.
The other approach - which may even be better (you'll have to test) would be to return the data vertically and perform the crosstab (matrix) in the report. So, instead of the three month columns you would have a single month column with the values 'Jan', 'Feb', 'Mar' and the value(s) for each. Then, you could let the report crosstab that as needed.

Second idea sounds good to me. Will the report return the columns named in the NVP table horizontally automatically and in the correct order? I ask because I've not used the matrix feature of reporting, yet.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

Right. The resource hit to do this in the database is not usually significant, however, it does "uglify" the code and lead to secondary costs as Jeff W pointed out. You would probably get better results handling this in the presentation layer.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeffrey Williams SSC Guru Points: 90313 More actions · Answer 9

Jeff Moden (8/3/2008)[hrSecond idea sounds good to me. Will the report return the columns named in the NVP table horizontally automatically and in the correct order? I ask because I've not used the matrix feature of reporting, yet.

Actually, this is something that I haven't done in reporting services yet. A long time ago I did something like that in Access, but I really don't know how it would work as far as order. To guarantee the order, you would want to use the month number - and I'm sure you could figure out how to convert the labels (if needed).

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs