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

KyleMit · Accepted Answer · 2014-11-19 13:43:03Z

6

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.

edited Nov 19, 2014 at 13:43

KyleMit♦

31.4k74 gold badges517 silver badges712 bronze badges

answered Mar 15, 2012 at 19:58

Michael Fredrickson

37.5k6 gold badges95 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mikebmassey Over a year ago

Thanks for the link. I believe you - but don't you think it's an inefficient way to query? Rather than calculate something once and use it 3 places, I know have to calculate it 3 separate times? Lame...

Michael Fredrickson Over a year ago

@mikebmassey I agree, it is lame... something I've had to do in the past when a calculation is very expensive... I have the first stored procedure do the calculation and save the results into a (non-temporary) table before returning its result, and then have the following stored procedures reference the table that was populated by the first call... Of course, this may be more difficult depending on how many different versions of the data exist / concurrency / etc...

mikebmassey Over a year ago

I didn't think about using a temp table. So, # would live, but not @ from query to query?

Michael Fredrickson Over a year ago

@mikebmassey actually, neither would live from query to query... so it would have to be a non-temporary table. Since there's a parameter provided to the report, you could include the parameter value as a column in your table, populate results for that parameter, and then select from the table again using that parameter.

Andrei Rantsevich · Accepted Answer · 2012-03-20 17:39:31Z

1

Just add one more param: ResultSetN and output corresponsing result set, depending of that param. 1 will return Orders 2 will return Results 3 will return Status You can then call your stored procedure 3 times with corresponding #.

EXEC pClientData @clientID ,1

EXEC pClientData @clientID ,2

EXEC pClientData @clientID ,3

answered Mar 20, 2012 at 17:39

Andrei Rantsevich

2,97523 silver badges25 bronze badges

Collectives™ on Stack Overflow

SSRS - return multiple queries in one stored procedure

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related