473,836 Members | 1,505 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Multiple Recordsets from 1 Stored Procedure

Hi there,
I have a Stored Procedure in SQL 2000 that takes 10 input paremeters,
and outputs 11 recordsets, not rows, but 11 different SELECT statements.

The purpose of this is to for a menu system in a store. If all the
inputs are NULL, then just the top level categories are output in the
first recordset. The ID of each category will then be passed back into
the stored procedure where it then filters the second recordset to
create the second level of categorisation.

Can ASP Classic with VBScript pull multiple recordsets from a stored
procedure? Can anyone point me to some online resources?

Cheers,

Steve
Jun 27 '08 #1
3 10044
"Dooza" <st*****@SPAM.d ooza.tvwrote in message
news:OH******** ******@TK2MSFTN GP05.phx.gbl...
Hi there,
I have a Stored Procedure in SQL 2000 that takes 10 input paremeters,
and outputs 11 recordsets, not rows, but 11 different SELECT statements.

The purpose of this is to for a menu system in a store. If all the
inputs are NULL, then just the top level categories are output in the
first recordset. The ID of each category will then be passed back into
the stored procedure where it then filters the second recordset to
create the second level of categorisation.

Can ASP Classic with VBScript pull multiple recordsets from a stored
procedure? Can anyone point me to some online resources?
The standard ADODB Recordset object will expose the first result set
returned.

When you have consumed that set use the method .NextRecordset( ) on the
recordset object. It will now exposed the second result set. Continue
doing that until all result sets are used.

See:-

http://www.4guysfromrolla.com/webtech/083101-1.shtml
--
Anthony Jones - MVP ASP/ASP.NET
Jun 27 '08 #2
Anthony Jones wrote:
"Dooza" <st*****@SPAM.d ooza.tvwrote in message
news:OH******** ******@TK2MSFTN GP05.phx.gbl...
>Hi there,
I have a Stored Procedure in SQL 2000 that takes 10 input paremeters,
and outputs 11 recordsets, not rows, but 11 different SELECT statements.

The purpose of this is to for a menu system in a store. If all the
inputs are NULL, then just the top level categories are output in the
first recordset. The ID of each category will then be passed back into
the stored procedure where it then filters the second recordset to
create the second level of categorisation.

Can ASP Classic with VBScript pull multiple recordsets from a stored
procedure? Can anyone point me to some online resources?

The standard ADODB Recordset object will expose the first result set
returned.

When you have consumed that set use the method .NextRecordset( ) on the
recordset object. It will now exposed the second result set. Continue
doing that until all result sets are used.

See:-

http://www.4guysfromrolla.com/webtech/083101-1.shtml
Hi Anthony, thank you, that should do the job perfectly!

Steve
Jun 27 '08 #3
Anthony Jones wrote:
"Dooza" <st*****@SPAM.d ooza.tvwrote in message
news:OH******** ******@TK2MSFTN GP05.phx.gbl...
>Hi there,
I have a Stored Procedure in SQL 2000 that takes 10 input paremeters,
and outputs 11 recordsets, not rows, but 11 different SELECT statements.

The purpose of this is to for a menu system in a store. If all the
inputs are NULL, then just the top level categories are output in the
first recordset. The ID of each category will then be passed back into
the stored procedure where it then filters the second recordset to
create the second level of categorisation.

Can ASP Classic with VBScript pull multiple recordsets from a stored
procedure? Can anyone point me to some online resources?

The standard ADODB Recordset object will expose the first result set
returned.

When you have consumed that set use the method .NextRecordset( ) on the
recordset object. It will now exposed the second result set. Continue
doing that until all result sets are used.

See:-

http://www.4guysfromrolla.com/webtech/083101-1.shtml
Have to say this is a brilliant way to work with stored procedures, I
wish I knew about it sooner!

Steve
Jun 27 '08 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1626
by: Dr. Killer D. Goat The Man Eater | last post by:
I have a stored procedure that returns multiple select statements. eg. select one from table_One Select two from table_two
7
16554
by: (Pete Cresswell) | last post by:
I posted this in the MS Access group, but no luck. ------------------------------------------ I've got another stored procedure in the same app that returns multiple recordsets and the code works. But now I've written another SP and the code traps out with the 3251 message. The SP is writing two recordsets. When I run the SP in Query Analyzer, both recordsets appear.
1
2970
by: Cliff | last post by:
here's my code: my $sth = $dbhSQL->prepare('{call proc(?,?,?)}'); $sth->bind_param(1,"asd"); $sth->bind_param(2,"klm"); $sth->bind_param_inout(3,\$no_go, 1000); $sth->execute; print "no go = $no_go\n"; while( my @row=$sth->fetchrow_array){
2
2261
by: Keith Chadwick | last post by:
I prefer to use stored procedures over having sql statements sitting in my ..NET code. My problem is I can not figure out how get .NET to understand there are multiple recordsets coming back. Each of the recordsets is returning xml data and if called individually would be called as : Dim myCmd as SqlCommand=New SqlCommand("ae_RegGetShow",myCnct) myDataSet.ReadXml(myCmd.ExecuteXmlReader(), XmlReadMode.Fragment)
12
17675
by: Scott | last post by:
Front-end Access 2000 I have a stored procedure that has 2 parameters BusinessUnitID and Year. It returns multiple record sets (5 to be exact). I thought I could use a Pass through query but that only returns the first record set. I thought I could use ADO but that does not seem to work. I get an Error
1
11853
by: Lyle Fairfield | last post by:
MS-SQL 2000, Access 2003, ADO 2.8 Please, consider the SQL string in the following code: Dim r As ADODB.Recordset Dim r2 As ADODB.Recordset Set r = CurrentProject.Connection.Execute( _ "SET NOCOUNT ON " _ & "SELECT * FROM FFDBATransactions" _ & "; SET NOCOUNT ON " _
16
5732
by: Randy Harris | last post by:
I was inspired by the recent discussion of returning multiple recordsets to ADO from a stored procedure. (Amazed is probably more accurate). I asked about how to accomplish same with Oracle and got a nudge in the right direction from Mr. Kreft. I promised to provide details once working, so here it is. The code is shown below. My next step is to build this technique into my application. I'm hoping for substantial performance gain. ...
1
11321
by: stjulian | last post by:
I have a stored procedure which returns 2 tables and 1 output value. I want the first table to be assigned to rs1 and the second to rs2. However when I run this, I get the following error as I begin to refer to the rs2 recordset (Do while not rs2.eof). I have even used 2 ".execute" statements in the code below, but the code assigns the first recordset twice. Microsoft VBScript runtime error '800a01a8' Object required: 'rs2' ...
4
6137
by: =?Utf-8?B?TWFkaXNvbg==?= | last post by:
Hi all, Could I use the sqldatasource with stored-procedure to return multiple recordset? I did not want to have any sqldatasourec in the one page just to return the record back to dropdownlists. In my stored procedure will have 2 select statement. If it can be done how I refer to each dropdownlist to correct recordset. Please advise. Thanks.
0
9671
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10846
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10551
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10254
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9379
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6979
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5650
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5828
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4021
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.