473,322 Members | 1,241 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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 9096
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: jamiemcc | last post by:
Hi, I would like to have 1 stored procedure call another stored procedure (which contains multiple select statements) and then be able to access the 3 result sets. Example Create Procedure ....
4
by: marc | last post by:
I've been developing a stored procedure that uses a user defined function in the query portion of the procedure. However, since the end product needs to allow for dynamic table names, the UDF will...
8
by: Thomasb | last post by:
With a background in MS SQL Server programming I'm used to temporary tables. Have just started to work with DB2 ver 7 on z/OS and stumbled into the concept of GLOBAL TEMPORARY TABLE. I have...
3
by: Irfan | last post by:
There are several ways of handling Transactions in DotNet. Some of them are 1. Using COM+ Serviced Component. 2. Using ADO .Net 3. using stored procedure What is the best way of handling...
2
by: Justin | last post by:
I am creating a web app for a client in VS.NET using ASP.NET with C#. I need to query three tables in a database using one parameter and display the results on the page. my question is should I use...
3
by: comp_databases_ms-sqlserver | last post by:
This post is related to SQL server 2000 and SQL Server 2005 all editions. Many of my stored procedures create temporary tables in the code. I want to find a way to find the query plan for these...
3
by: Otto Carl Marte | last post by:
Hi, As I understand it, Declared Global Temporary Tables (DGTTs) have a scope that is session/connection based. Using the same connection, I have discovered that if I declare a DGTT in one...
4
by: yin_n_yang74 | last post by:
I am new to SQL and SQL Server world. There must be a simple solution to this, but I'm not seeing it. I am trying to create a crystal report (v8.5) using a stored procedure from SQL Server...
2
by: =?Utf-8?B?VGVycnk=?= | last post by:
I have coded multiple select statements in a single stored procedure, and when I execute this procedure on SQL Server Management Express, I correctly get multiple result sets. But, if I try to add...
5
by: Bogdan | last post by:
Hi, I have a stored procedure that uses JOINs to return columns from multiple tables. I also have another stored proc that that takes a series of params and updates multiple tables. I used the...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.