dbStoredProcRS

Retrieve a recordset from a stored procedure returning multiple recordsets.  At this time only MS-SQL is known to return multiple recordsets under ADO. Recordset table is returned in a grid format

Property Syntax

Obj.dbStoredProcRS=RS_Number

Parameters

Parameters

Default Value

Description

RS_Number

1

Recordset Number

Example #1 - Retrieve as normal recordset

The following is the listing of stored procedure "reptq3" of the MS-SQL "Pubs" database -

CREATE PROCEDURE reptq3 @lolimit money, @hilimit money,
@type char(12)
AS
select pub_id, type, title_id, price
from titles
where price >@lolimit AND price <@hilimit AND type = @type OR type LIKE '%cook%'
order by pub_id, type
COMPUTE count(title_id) BY pub_id, type

Use the SQL Server query Analyzer, let's set the lolimit, hilimit and %cook% values as 5,50,'business'. The result will look like the following -

Example # 1 Output

Now, let's execute the following ASPDB2000 code and retrieve the 5th recordset. Note that the "cnt" is returned as a recordset.

<%
Set X=Server.CreateObject("ASP.DB")
X.dbQuickProps= "(|,)5|DSN=pubs;UID=sa;PWD=;||grid|4|||SQL|2"
X.dbStoredProc="reptq3,4,5,50,'business'"
X.dbStoredProcRS=5
X.aspdb2K
%>

This example executes the MS-SQL pubs reptq3 stored procedure and retrieve the 5th recordset and the browser output is as follows -

Browser Output

if you retrieve the 6th recordset, set X.dbStoredProcRs=6.  The browser output will be as follows -

Browser Output

Example #2 - Retrieve as session variable object for template

Another way to retrieve multiple recordsets is to save them as session variables for the purpose of placing them in the  template. The following example retrieves the 1st, 3rd and 5th recordsets and saves them in the session variables via the "Grid" option of the dbMagicLayout property. Instead of using a template, the following code just writes out the session variable.

<%
Set SQL7=Server.CreateObject("ASP.DB")
SQL7.dbQuickProps= "(|,)3|DSN=pubs;UID=sa;PWD=;||grid|4|||SQL|1000"
SQL7.dbStoredProc="reptq3,4,5,50,'business'"
SQL7.dbOptions="CellFontTag=Size=2, HeaderFont=Size=2"
SQL7.dbDisplay=false
for i=1 to 5 step 2
    SQL7.dbStoredProcRS=i
    SQL7.dbMagicLayout=i & ";GRID"
    SQL7.dbMagicLayout=i & ";GRID"
    SQL7.aspdb2K
    response.write(session("aspDB_Layout_3_" & i) & "<BR>")
next
%>

Example #2 - Output