December 15, 2011 at 10:19 am
Hello all,
It's me again. As anyone who has read my posts before, you know it's amateur hour for me and SSRS. I have a pretty generic question and hope someone can provide an answer with a SIMPLE example.
Can someone explain to me how output parameters work and how they are written in a query/stored procedure?
I have a query drawn up, but I want to allow the end-user to be able to select the output choices (be it whatever). For example, the result of the query will kick out 10 fields, but what if the user only wants to see two of them? How is that written into a query and ultimately reach the report? When written into the query, does the end-user see a drop-down box with output choices? I have no idea
My IT folks aren't much help so I'm turning to the SSC faithful for guidance! Any help is MUCH appreciated.
December 15, 2011 at 10:49 am
What you're looking for isn't an output parameter.
Output parameters look like this:
create stored procedure dbo.Hello
(@YourName_in varchar(100),
@Hello_out varchar(100) OUTPUT)
as
set @Hello_out = 'Hello ' + @YourName_in;
You assign a value to them in the procedure, and they are output to the whatever called the procedure.
declare @Message varchar(100);
exec dbo.Hello @YourName_in = 'GSquared', @Message = @Hello_out OUTPUT;
print @Message;
When you call them, you again use the Output keyword.
That's all they are. Not much use in reports. Most database developers are more likely to use a user-defined-function than they are to use a stored procedure with an output parameter, and they do similar things.
If you want to regulate the columns that a user views, you either need to do that with a filter in the report (most common solution), or you need to use dynamic SQL in the stored procedure, with an input parameter that lists the columns desired, and then the report needs to programmatically redefine itself so it can run on a variable dataset from the procedure. The second one is complex, difficult to debug, and usually slow to run. The first option, of building a report that can dynamically hide/deisplay columns, like a pivot table report, is usually much, much easier.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 15, 2011 at 10:54 am
Whoa, sounds complicated, but thank you very much for the quick response. I'm sure I'll have more questions once I dive in.
Very helpful reply, so again, thank you!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply