Multiple Recordsets from 1 Stored Procedure
in [ASP]
|
Prev: File System Object Help
Next: FREE Tutorials on HTML XHTML CSS JavaScript XML XSL ASP SQL ADO VBScript, SAP - ABAP and more...
From: Dooza on 5 Jun 2008 06:15 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
From: Anthony Jones on 5 Jun 2008 06:32 "Dooza" <steveNO(a)SPAM.dooza.tv> wrote in message news:OHwrDUvxIHA.2360(a)TK2MSFTNGP05.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
From: Dooza on 5 Jun 2008 06:37 Anthony Jones wrote: > "Dooza" <steveNO(a)SPAM.dooza.tv> wrote in message > news:OHwrDUvxIHA.2360(a)TK2MSFTNGP05.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
From: Dooza on 5 Jun 2008 07:03
Anthony Jones wrote: > "Dooza" <steveNO(a)SPAM.dooza.tv> wrote in message > news:OHwrDUvxIHA.2360(a)TK2MSFTNGP05.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 |