Can multiple DataSet tables be returned from a single Stored Procedure call to SQL Server?

[Bill]

I am having some serious performance problems resulting from too many round
trips on the wire in order to populate several tables in an ADO.NET DataSet.
I would like to make ONE stored procedure call that will return all of the
table data. (I am using SQL Server 2000)

If this is doable, does anyone have an example of how to do it? (For
example, a call that will return -- say -- 100 PO header records and 500
related detail items in two tables.)

 

[Chuck]

Another solution is to build a server (Data tier) object that resides on the
same computer as the remote database.
When I want to get any tables, I send a message with any filters, etc. in
the parameters. The remote method does all of the work of populating the
desired tables. It then returns a dataset with all of the tables I want. I
then merge the returned dataset into my local dataset or use it as is. This
results in just one roundtrip. The downside is supporting a remote object
with lifetime and other problems.

Chuck