Select multiple table in 1 stored proc

  • Thread starter Thread starter Brandon Fraser
  • Start date Start date
B

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
 
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
 
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
Click to expand...
Click to expand...
 
Yes, it does.

-Roger

Uri Dor said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top