Creating a multi table select stored procedure

[Karl]

Am in the process of learning to use C#.

Need to create a stored procedure that returns a dataSet
containing several tables and then access this query from
an object.

 

[Guest]

Hey Karl

All you need to do is create a stored procedure that has a bunch of select statements in i

CREATE PROCEDURE usp_GetSomeStuf
A
SELECT * FROM Table
SELECT * FROM Table
SELECT * FROM Table

The following code will allow you to access each table

SqlConnection myConnection = new SqlConnection ({ConnectString})
myConnection.Open()
SqlCommand command = new SqlCommand("usp_GetSomeStuff", myConnection)
DataSet ds = new DataSet()
SqlAdapter adapter = new SqlAdapter(command)
adapter.fill(ds)

ds.Tables[0] now references your first select, ds.Tables[1] references your second select, etc.

 

[Guest]

HI Karl,
How about the performance will affect if you run
different stored procedures for each select statemnt
instead of group which one is better.
Thanks
suda

-----Original Message-----
Hey Karl,

All you need to do is create a stored procedure that has

a bunch of select statements in it

CREATE PROCEDURE usp_GetSomeStuff
AS
SELECT * FROM Table1
SELECT * FROM Table2
SELECT * FROM Table3

The following code will allow you to access each table:

SqlConnection myConnection = new SqlConnection ({ConnectString});
myConnection.Open();
SqlCommand command = new SqlCommand("usp_GetSomeStuff", myConnection);
DataSet ds = new DataSet();
SqlAdapter adapter = new SqlAdapter(command);
adapter.fill(ds);

ds.Tables[0] now references your first select, ds.Tables

[1] references your second select, etc.