Stored Procedure Multiple Tables

[Chris]

This might be a stupid question.... I have a stored procedures, which uses
two selects. When I run the SP I get two resultsets, one very big and the
other much smaller, which is the one I want in the dataset. How do a ensure
only the last one goes in the dataset.

 

[Mark Rae]

This might be a stupid question.... I have a stored procedure, which uses
two selects. When I run the SP I get two resultsets, one very big and the
other much smaller, which is the one I want in the dataset. How do a
ensure only the last one goes in the dataset.

Well, firstly do you need the SP to return both resultsets? If not, get rid
of the one you don't need...

If you do, ignore the one you don't need in the DataSet.

E.g., if you are trying to bind the smaller of the two resultsets into a
GridView, you could do something like this:

DataSet MyDS = <run your SP>;
MyGridView.DataSource = MyDS.Tables[1];
MyGridView.DataBind();

 

[Guest]

To Add to what Mark has suggested, use an extra parameter to the Stored
procedure whether to do the first select statement using a T-Sql if
condition. If we do so we can avoid populating the dataset with unwanted
data. Binding to the Gridview would now be as follows as we would expect only
one resultset from the stored proc:

DataSet MyDS = <run your SP>;
MyGridView.DataSource = MyDS.Tables[0];
MyGridView.DataBind();

HTH
Siva

This might be a stupid question.... I have a stored procedure, which uses
two selects. When I run the SP I get two resultsets, one very big and the
other much smaller, which is the one I want in the dataset. How do a
ensure only the last one goes in the dataset.

Well, firstly do you need the SP to return both resultsets? If not, get rid
of the one you don't need...

If you do, ignore the one you don't need in the DataSet.

E.g., if you are trying to bind the smaller of the two resultsets into a
GridView, you could do something like this:

DataSet MyDS = <run your SP>;
MyGridView.DataSource = MyDS.Tables[1];
MyGridView.DataBind();

 

[sloan]

My suggestion (which is a alternate of the flag being sent is ) is:


Create 2 procedures.


uspEmployeeGetAll
Select EmpID, LastName, FirstName from dbo.Employee


uspDepartmentGetAll
Select DeptID, DepartmentName from dbo.Department

Then a wrapper usp


uspEmployeesAndDepartmentsGetAll
EXEC dbo.uspEmployeeGetAll
EXEC dbo.uspDepartmentGetAll



That'll work.


If you use an IDataReader, you have more control over loading data. But you
pay for it with more code.

See my blog for an example:
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!140.entry

 

[Rad [Visual C# MVP]]

This might be a stupid question.... I have a stored procedure, which
uses two selects. When I run the SP I get two resultsets, one very big
and the other much smaller, which is the one I want in the dataset.
How do a ensure only the last one goes in the dataset.

Well, firstly do you need the SP to return both resultsets? If not, get
rid of the one you don't need...

If you do, ignore the one you don't need in the DataSet.

E.g., if you are trying to bind the smaller of the two resultsets into a
GridView, you could do something like this:

DataSet MyDS = <run your SP>;
MyGridView.DataSource = MyDS.Tables[1];
MyGridView.DataBind();

An additional benefit of doing this is that it will even run faster!