Multiple Recordsets from 1 Stored Procedure

[Dooza]

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

[Anthony Jones]

"Dooza" <st*****@SPAM.dooza.tvwrote in message
news:OH**************@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

[Dooza]

Anthony Jones wrote:

"Dooza" <st*****@SPAM.dooza.tvwrote in message
news:OH**************@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

[Dooza]

Anthony Jones wrote:

"Dooza" <st*****@SPAM.dooza.tvwrote in message
news:OH**************@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