SSRS - return multiple queries in one stored procedure

Question

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

When I create the SSRS report, I point the datasource to this stored procedure, but only the result set 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 those 3 BEGIN/END result sets into a single report?

Sample code:

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

Query call from SSRS:

EXEC pClientData @clientID

Michael Fredrickson Michael Fredrickson · Accepted Answer · 2012-03-15T19:58:57

Unfortunately, this is not possible.

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

If a stored procedure returns multiple rowsets (executes multiple SELECT statements), only the first rowset is processed by the report. If you need all results, consider implementing a wrapper stored procedure that merges the multiple rowsets in a temporary table and returns all rows with one SELECT statement.

As suggested, you'll have to make some sort of adjustment to your stored procedure in order to accomplish this. Either create a wrapper to return all results in a single set, or split your existing stored procedure into three.

Note: At the moment, you can get a pdf of the ebook here, but it might get taken down.

SSRS - return multiple queries in one stored procedure

2 Answers