Select multiple table in 1 stored proc

[Brandon Fraser]

Hi All,

Does anyone know how to do this or give an idea how can I achieve this using
DataSet and ADO.NET

For example I have 2 table relationships.
Table1 -> Customer
Table2 -> CustomerOrder

The name should be pretty much self explanatory. I want to create a stored
proc that returns the customers and the customer's order given a some
parameter. And the content should be stored in different DataTable inside a
DataSet.

First question: How do I create a stored proc that returns 2 different
tables? Can I do this in MS SQL Server?

Secondly how do I populate the DataSet with the two different DataTables?
I'm trying to avoid two stored proc call for populating the two different
DataTable in my DataSet.

Thanks,

Brandon

 

[Roger W.]

Hi Brandon,

It's actually really simple - you just put multiple SELECT statements in
your stored procedure, one after the other, and your DataSet will
automatically get populated with two tables, which you can address as
myDs.Tables[0] and myDs.Tables[1] , etc.

So in the main section of your stored procedure you'd just say:

SELECT * FROM Customer
SELECT * FROM CustomerOrder

I hope this helps!

-Roger

 

[Uri Dor]

does this work in .NET 1.1?

Hi Brandon,

It's actually really simple - you just put multiple SELECT statements in
your stored procedure, one after the other, and your DataSet will
automatically get populated with two tables, which you can address as
myDs.Tables[0] and myDs.Tables[1] , etc.

So in the main section of your stored procedure you'd just say:

SELECT * FROM Customer
SELECT * FROM CustomerOrder

I hope this helps!

-Roger

Hi All,

Does anyone know how to do this or give an idea how can I achieve this
using
DataSet and ADO.NET

For example I have 2 table relationships.
Table1 -> Customer
Table2 -> CustomerOrder

The name should be pretty much self explanatory. I want to create a stored
proc that returns the customers and the customer's order given a some
parameter. And the content should be stored in different DataTable inside
a
DataSet.

First question: How do I create a stored proc that returns 2 different
tables? Can I do this in MS SQL Server?

Secondly how do I populate the DataSet with the two different DataTables?
I'm trying to avoid two stored proc call for populating the two different
DataTable in my DataSet.

Thanks,

Brandon

 

[Roger W.]

Yes, it does.

-Roger

does this work in .NET 1.1?

Hi Brandon,

It's actually really simple - you just put multiple SELECT statements in
your stored procedure, one after the other, and your DataSet will
automatically get populated with two tables, which you can address as
myDs.Tables[0] and myDs.Tables[1] , etc.

So in the main section of your stored procedure you'd just say:

SELECT * FROM Customer
SELECT * FROM CustomerOrder

I hope this helps!

-Roger

Hi All,

Does anyone know how to do this or give an idea how can I achieve this
using
DataSet and ADO.NET

For example I have 2 table relationships.
Table1 -> Customer
Table2 -> CustomerOrder

The name should be pretty much self explanatory. I want to create a
stored
proc that returns the customers and the customer's order given a some
parameter. And the content should be stored in different DataTable inside
a
DataSet.

First question: How do I create a stored proc that returns 2 different
tables? Can I do this in MS SQL Server?

Secondly how do I populate the DataSet with the two different DataTables?
I'm trying to avoid two stored proc call for populating the two different
DataTable in my DataSet.

Thanks,

Brandon