Report from prcedure that return multiple recordsets

Hi there,

I am creating a report from a stored procedure that returns

multiples record sets. For an example, my stored procedure is as follows

CREATE PROCEDURE MYPROCEDURE
AS

BEGIN
SELECT * FROM TABLE1
SELECT * FROM TABLE2
SELECT * FROM TABLE3
END

Now lets say I want to create a report that will display the

result of the query no 2 (which is SELECT * FROM TABLE2 in this example). How

can I do this? Is there any way to bind the second record set to the report

dataset with out changing the stored procedure?
I will appreciate any kind of suggestions on this

Thanks
Moim

yes,

you can do this in ur procedure itself. based upon what criteria you decide which query to run, pass that criteria to procedure as a parameter, and then use if condition to run the query

|||Suresh,
Thanks for your reply. But I am sorry I dont have a chance to modify the Stored procesdure. Those are written for a legacy software and they are running good for couple of years.
So can you or any one else find me an alternate to do this?

Anyway, thanks shuresh.

Thanks
Moim|||

Moim,

Unfortunately, SSRS doesn't support multiple recordsets being returned from a stored procedure. It will use the first resultset returned only.

Can you break up your master stored proc into 3 smaller stored procs, each called by separate reports with it's own resultset?

Jarret

|||Hmmm..seems I have to do that Jarret. Anyway thanks for your reply.|||

Can you mark this one as answered so others can see that multiple recordsets aren't supported?

Thanks.

Jarret

|||Sure. Thanks|||

Thanks Jarret for the answer. I did that earlier , but couldn't get my procedure to work by passing parameters to get the resulting resultset. Insted I have created different procedures and made it to work.

I didn't know that this is a constraint with SSRS.