locked
Multiple resultsets from StoredProcedure SSRS RRS feed

  • Question

  • User472798847 posted

    Hi,

    I have a stored procedure that returns multiple resultsets just as below

    Create StoredProcedure sp_MultipleDataSets as begin SELECT EMPID, ENAME, JOB, SAL, DEPTID FROM EMP -- first result set SELECT DEPTID, DNAME, LOC FROM DEPT --second result set end 

    In SSRS, while creating a new report i configured the stored procedure for dataset. It creates the dataset ONLY with the columns returned from the first result set. It does not identify the second result set.

    How can I create datasets for both the result sets from a stored procedure like above

    Friday, September 7, 2012 7:01 AM

Answers

  • User2105670541 posted

    A straight answer: we cannot have multiple resultset returned from a single dataset.

    Workaround: you need to create seperate dataset for your each resultset

    OR you need to combine your result set query using SQL UNION statement

    OR you can create a single dataset with a parameter and based on the parameter you get your desired resultset (you need to tweak the query), and you call the same dataset using different parameter value differnt times.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 12, 2012 1:03 AM

All replies

  • Friday, September 7, 2012 7:14 AM
  • User2105670541 posted

    A straight answer: we cannot have multiple resultset returned from a single dataset.

    Workaround: you need to create seperate dataset for your each resultset

    OR you need to combine your result set query using SQL UNION statement

    OR you can create a single dataset with a parameter and based on the parameter you get your desired resultset (you need to tweak the query), and you call the same dataset using different parameter value differnt times.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 12, 2012 1:03 AM
  • User1977787504 posted

    if you have more than one dataset ,then there is a way to sync (merge) it in one if there is any relation between the table..,, it would be large dataset .. but it will definitely work

    Friday, December 7, 2012 4:22 AM