locked
Can't SSRS 2008 handle stored procedures which return multiple tables? RRS feed

  • Question

  • User1866500947 posted

    I tried it and it only returns the first table.

    Any suggestion?

    Thank you,

    Sean



    Wednesday, October 13, 2010 10:24 AM

Answers

  • User1471008070 posted

    Hi,

    Reporting Services supports stored procedures that return only one set of data. If a stored procedure returns multiple result sets, only the first one is used.

    So here you have to join these tables together to return just one table or create separate stored procedures, and separate datasets to solve your problem.

    Thanks,

    Challen Fu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 19, 2010 4:15 AM

All replies

  • User-60558687 posted

    Try inserting the result sets coming from your stored proc into a table variable and then select * from your table variable. 

    here is an example

    Use SomeTestingDB;
    Go
    create proc testMRS
    as
    begin
    select 'A' C1 ,'AA' as C2
    select 'B' C1,'C' as C2
    end
    GO
    declare @t table (col1 varchar(50),col2 varchar(50));
    insert into @t(col1,col2)
    exec testMRS;
    
    select * from @t;
    GO
    drop proc testMRS



    Wednesday, October 13, 2010 3:16 PM
  • User1471008070 posted

    Hi,

    Reporting Services supports stored procedures that return only one set of data. If a stored procedure returns multiple result sets, only the first one is used.

    So here you have to join these tables together to return just one table or create separate stored procedures, and separate datasets to solve your problem.

    Thanks,

    Challen Fu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 19, 2010 4:15 AM