SSRS - returns multiple queries in one stored procedure

Question

I am trying to create a new SSRS report that will return and display SQL Server stored procedure values. I will pass the @clientID parameter to the stored procedure. This parameter is used in 3 different BEGIN / END statements. Each BEGIN `END` statement takes this parameter and makes a request, returning specific data.

When I create an SSRS report, I point to the data source for this stored procedure, but only the result obtained from the first BEGIN / END statement is returned. If I run the stored procedure in SSMS, I get 3 different result sets, as expected.

How can I get these 3 BEGIN / END result sets in one report?

Code example:

 CREATE PROCEDURE pClientData (@clientID varchar(30)) AS DECLARE @Orders table ( ... ); DECLARE @Results table ( ... ); DECLARE @Status table ( ... ); BEGIN SET NOCOUNT ON; -- Get all the orders by client INSERT INTO @Orders SELECT ... -- Return the results -- SELECT * FROM @Orders; END BEGIN SET NOCOUNT ON; -- Determine the Results INSERT INTO @Results SELECT ... SELECT * FROM @Results; END BEGIN SET NOCOUNT ON; SET @Status = ( SELECT ... ); SELECT @Status as Status; END GO 

Request from SSRS:

 EXEC pClientData @clientID 

Answer 1

Unfortunately this is not possible.

According to Microsoft SQL Server 2008 Reporting Application Services
From Section 4.3.5 - Working with Stored Procedures :

If a stored procedure returns multiple sets of rows (executes multiple SELECT ), the report processes only the first set of rows. If you want all the results, consider implementing a wrapper stored procedure that combines multiple sets of rows in a temporary table and returns all rows with a single SELECT .

As suggested, you will need to make some kind of adjustment to your stored procedure in order to accomplish this. Either create a wrapper to return all the results in a single set, or split the existing stored procedure into three.

Note At the moment, you can get pdf books here , but it can be removed.

Answer 2

Just add one more parameter: ResultSetN and the result corresponding to the result set, depending on this parameter. 1 returns orders 2 returns results 3 returns status Then you can call your stored procedure 3 times with the corresponding #.

EXEC pClientData @clientID, 1

EXEC pClientData @clientID, 2

EXEC pClientData @clientID, 3