Multiple resultsets from StoredProcedure SSRS

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

Answer 1

User320810878 posted

Hi ...look at these threads

http://forums.asp.net/t/1770328.aspx/1

http://forums.asp.net/t/1220097.aspx/1

Answer 2

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.

Answer 3

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