Multiple resultsets

Question

Multiple resultsets

Gary Dale

Old Hand

Points: 345
More actions
November 26, 2005 at 3:42 pm

#102960

Is it possible (with SQL2005), from a single stored procedure, to return multiple resultsets?
I want to know whether I could return resultsets from a number of tables from a single stored procedure.
From a .net 2.0 web service I have to return dataset objects which contain several tables. I want to know whether I have to obtian this data from multiple SP's or whether I could return it somehow from a single SP.
There are some cases where I must return the data in a single transation to ensure data consistency.
Many thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

Yes. For instance, take the following code:

USE AdventureWorks
GO

CREATE PROC Production.usp_MultipleRS
AS
BEGIN
SELECT * FROM Production.Product;
SELECT * FROM Production.ProductDescription;
END
GO

If you run EXEC Production.usp_MultipleRS from the AdventureWorks database, you'll see two resultsets returned.

K. Brian Kelley
@kbriankelley

Gary Dale Old Hand Points: 345 More actions · Answer 2

OK, thanks for that.

Perhaps my next question requires posting elsewhere but here goes.

Can I retrieve these two resultsets directly into an ADO.NET 2.0 dataset into the proper DataTables each with a different structure?

Regards

BeerBeerBeer SSCarpal Tunnel Points: 4021 More actions · Answer 3

It is my understanding that having executed this stored proc from within .Net code, you get back a dataset that has multiple tables - each with their own structure. All you need to do is determine which table is which.

I believe the table name will relect the tablename from the database.

Regards,

Steve

Life without beer is no life at all

All beer is good, some beers are just better than others

James Rimell Right there with Babe Points: 791 More actions · Answer 4

If you do not know the table name then it is a simple matter of getting it by index

i.e.

Me.myDataSet.Tables(0), Me.myDataSet.Tables(1)

etc

Gary Dale Old Hand Points: 345 More actions · Answer 5

OK, I have a number of SP's that return an entire lookup table each (they are only ever going to be a handful of records long) so I guess I create a new SP thus:

Create

Procedure GetAllLookups

AS

Exec

SaleType_Sel

Exec

ProdType_Sel

Exec

LineType_Sel

GO

but I when I call this from VS2005 RTM (admittedly auto-generated code) I only get back a single datatable in my dataset being the first returned. Clearly I am doing something wrong...

Gary Dale Old Hand Points: 345 More actions · Answer 6

I've discovered that if I create an untyped dataset in code (ie not use a designer) then it works!

I wonder if there is a way, using Add -> New Item... -> Dataset to create a typed dataset that will pick up all the returned tables?

Regards

James Rimell Right there with Babe Points: 791 More actions · Answer 7

I stay away from the designer for these very reasons, never really know what they are doing all my stuff is straight code