Append another Item to the Parameter List

Question

Append another Item to the Parameter List

venm86

SSC-Addicted

Points: 401
More actions
June 5, 2008 at 2:25 pm

#208163

Hi,
My report uses a parameter called "Publications" which I am filling with values from a stored procedure.
Issue is: Along with Stored Procedure values I also need to Add another list Item called "All" in that case I can display data regarding all publications.
But How can I Add different Item ("All") to my parameter list which is not present in the Stored procedure.
Any help is greatly appreciated.

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

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

chandra.pottipati SSC Veteran Points: 238 More actions · Answer 1

I know you are trying not to modify the stored procedure. But I don't think there is a way to add additional values to a dataset that is fetched from stored procedure.

We usually do queries like this

select value, description from view

union

select '%','All'

and use a "like" in the actual data query.

good luck

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

I agree... it'll likely require a change to the sproc or a bit of very clever SQL Injection and then only if the sproc is vulnerable.

--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)

venm86 SSC-Addicted Points: 401 More actions · Answer 3

Thank you guys. I guess I will change my stored proc as suggested.

matzer SSCrazy Eights Points: 9727 More actions · Answer 4

You can use a script simular like this (set Command Type of the dataset to Text):

set nocount on

create table #temp

(

Publication varchar(50)

)

insert into #temp

execute sp_GetPublications

select * from #temp

union all

select 'All'

drop table #temp

Replace sp_GetPublications with your stored procedure. You will get an error when you enter this script. Add the field publication manually to your dataset (Field Name=Publication, Type=Database Field, Value=Publication).

Peter