468,103 Members | 1,333 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,103 developers. It's quick & easy.

Stored Procedure Multiple Tables

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.
Jul 1 '07 #1
4 8921
"Chris" <no****@nospam.comwrote in message
news:Og**************@TK2MSFTNGP06.phx.gbl...
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();
--
http://www.markrae.net

Jul 1 '07 #2
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
"Mark Rae" wrote:
"Chris" <no****@nospam.comwrote in message
news:Og**************@TK2MSFTNGP06.phx.gbl...
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();
--
http://www.markrae.net

Jul 1 '07 #3


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

"Chris" <no****@nospam.comwrote in message
news:Og**************@TK2MSFTNGP06.phx.gbl...
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.


Jul 3 '07 #4
Mark Rae wrote:
"Chris" <no****@nospam.comwrote in message
news:Og**************@TK2MSFTNGP06.phx.gbl...
>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! :)

--
http://bytes.thinkersroom.com
Jul 3 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Thomasb | last post: by
2 posts views Thread by Justin | last post: by
4 posts views Thread by yin_n_yang74 | last post: by
2 posts views Thread by =?Utf-8?B?VGVycnk=?= | last post: by
1 post views Thread by Solo | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.