July 6, 2007 at 3:04 pm
Can SSRS handle a Stored Procedure with multiple result sets?
I have a SP that looks like this & returns two results, but SSRS only sees the first result set.
******************************************
Create procedure [dbo].[getEmployeeReport]
@ClassID smallint
as
SELECT * from Employees WHERE (ClassID = @ClassID)
SELECT * from Attendance WHERE (ClassID = @ClassID)
******************************************
Thanks,
James H.
July 6, 2007 at 3:31 pm
Here's one question :
What do the employees have to do with the attendance in the same resultset?
I'd do 2 reports and show the 2 reports on a master report that would make it seem like it's one... assuming that it is what is required.
July 6, 2007 at 4:11 pm
That query was just a sample, not the actual one I am trying to use...
I found something on MSDN that says you can only have one result set in a stored procedure is you want to use it in SSRS.
http://msdn2.microsoft.com/en-us/library/ms159260.aspx
The reason that I wanted to put all of my results in the sample stored procedure is so that I wouldn't need to have 4 or 5 stored procedures per report. It would be easier to manage with them all in one procedure. Oh well.
James H.
July 6, 2007 at 4:28 pm
How would that make it easier?
July 6, 2007 at 10:52 pm
James -
Per the documentation, you can only have one resultset per stored procedure... you can have multiple datasets that utilize the same procedure (if you really want to you could make a single stored procedure that returns different results based on a parameter, e.g. if option=a select * from employee...) but stored procedure can only return one set of results per call.
Joe
July 31, 2007 at 4:09 pm
Joe, it makes perfect sense that the sp can only return one result set for the dataset. I think I'm working through the same problem as James. In my sp, I can pass a parameter value 0-9. I'll get a different result set (meaning field names and values) depending on when number 1-9 that I pass through, i.e. passing 1 might give me 3 fields, passing 2 might give me 6 different fields, etc, and they are not the same type of data or number of records. I can see all of these fine in Query Analyzer. If I pass a 0, I get 9 separate result sets. The problem I have is that if I want to pass 2 in Reporting Services, I can't get that field list to pull in to my dataset; instead, the dataset will only pick up the 1st result set field list. So in the example above, I'm getting 3 fields, different from the 6 I wanted. I can run execute the sp while working in the dataset, and pass it a 2, and see the 6 fields that I want with the associated records, I just can't pull those fields into my dataset.
In the first version, the one that worked with SQL Server 2000, I could for sure pass a 2 and see get the fields from the second result set. In fact, in that version, I actually had to execute the sp and press the refresh button to get the field list to load. But in 2005, the field list seems to load itself, without me ever having to refresh the dataset.
Does this make sense? I just want to be able to pull in the 2nd (or 3rd, etc) result set by using the appropriate parameter without having to break up the sp into 9 smaller sp's.
July 31, 2007 at 9:36 pm
Daniel -
I can think of at least one way to handle this one:
Develop your stored procedure to return the same number of fields and the same for all of your fields (e.g. option/parameter 1 = select name as fielda, address as fieldb from x, option/parameter 2 = select phone as fielda, email as fieldb). Given a consistent number of fields returned by the stored procedure you could use if statements, tables that display/don't display based on the parameter, etc. or some such to display the data in the correct order/format?
Joe
August 1, 2007 at 8:44 am
I actually used Joe's method on one of the last reports I did. It required a little bit more programming on the SSRS side, but it worked. But, I still fill like it was a workaround for a problem. (Problem: SSRS can't handle multiple resultset from a stored procedure.) There are many other programming languages that can handle multiple resultsets from a stored procedure. I don't know why Micro$oft didn't include it in SSRS.
Not to get on a MS rant, but they tent to build software the quickest way possible until the community asks for certain enhancements. They probably looked at handling multiple resultsets and decided it would be too much work to manage them. I work with an ex-MS developer. They are lazy.
Getting back to the point. I have a report that has like 6 resultset required and in my SQL DB, I have 6 stored procedures. I think its cleaner to have all the queries in one procedure, for code management, and to also reduce the number of roundtrips you need to make to the database. 6 stored procedures = 6 sets of parameters & 6 sets of resultsets, sent one at a time.
James
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply