Returning Multiple Resultsets from an SP

Question

Returning Multiple Resultsets from an SP

Hatim Ali

Old Hand

Points: 375
More actions
July 31, 2004 at 12:36 am

#108671

Hi,
How can i retrun two resultsets from a stored procedure.
Regards,
Hatim Ali.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Don Millhouse SSC Eights! Points: 977 More actions · Answer 1

For what it is worth, here are a few thoughts:

1. What about calling the stored procedure twice, passing an argument each time to indicate the result set you are looking for?

2. You could combine the result sets with a UNION.

3. Use two different procedures.

4. Use two select statements.

Andy Warren SSC Guru Points: 119902 More actions · Answer 2

You can return two resultsets just be doing two selects:

select top 10 * from tablea

select top 10 * from tableb

Both ADO and ADO.Net support it, though in both it's up to identify which resultset is which based on the order you retrieved them. The advantage is one round trip instead of two.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter