Multiple Resultsets From A Stored Procedure

Advantages to returning multiple resultsets: - Reduce network traffic slightly, in that your script only makes the ODBC / ADO / OLEDB call once instead of 6 times for 6 resultsets. - have one procedure that collects all data required for a given web page.

Difference Between CF and ASP: - In CF the code is still pretty clean. You assign a query name to each of the returned resultsets and can access them out of order if you want.

- In ASP w/ADO, you access the first, then the second, then the 3rd, etc... and (AFAIK) there's no going back to the previous set.

Sample code for CF:

<CFSTOREDPROC  
    PROCEDURE="spMyProcName"  
    DATASOURCE="myDataSource"  
    USERNAME="myusername"  
    PASSWORD="mypassword"  
    RETURNCODE="Yes">  
        <CFPROCPARAM  
            TYPE="In"  
            CFSQLTYPE="CF_SQL_VARCHAR"  
            DBVARNAME="@myname"  
            VALUE="#session.myname#"  
            NULL="No">  
        <CFPROCPARAM  
            TYPE="In"  
            CFSQLTYPE="CF_SQL_VARCHAR"  
            DBVARNAME="@targetname"  
            VALUE="#session.targetname#"  
            NULL="No">  
        <CFPROCPARAM  
            TYPE="Out"  
            CFSQLTYPE="CF_SQL_VARCHAR"  
            DBVARNAME="@NewKey"  
            CFVARNAME="MyNewKey"  
            NULL="Yes">  
<!--- List of results ---> 
    <CFPROCRESULT  
        NAME="firstresult">  
    <CFPROCRESULT  
        NAME="secondset"  
        RESULTSET="2">  
    <CFPROCRESULT  
        NAME="thirdset"  
        RESULTSET="3">  
    <CFPROCRESULT  
        NAME="fourthset"  
        RESULTSET="4">  
    <CFPROCRESULT  
        NAME="fifthset"  
        RESULTSET="5">  
    <CFPROCRESULT  
        NAME="sixthset"  
        RESULTSET="6">  
    <CFPROCRESULT  
        NAME="seventhset"  
        RESULTSET="7">  
    <CFPROCRESULT  
        NAME="eighthset"  
        RESULTSET="8">  
</CFSTOREDPROC>  

ASP Sample Code:

mk:@MSITStore:C:\Program%20Files\Common%20Files\System\Ado\ado210.chm::/htm/mdmthnextrecx.htm

(or just look up the keyword 'Nextrecordset')

Dim rstCompound as ADODB.Recordset ...
Set rstCompound = New ADODB.Recordset rstCompound.Open "SELECT * FROM authors; " & _ "SELECT * FROM stores; " & _ "SELECT * FROM jobs", strCnn, , , adCmdText
intCount = 1 Do Until rstCompound Is Nothing 
Debug.Print "Contents of recordset #" 
& intCount Do While Not rstCompound.EOF Debug.Print , rstCompound.Fields(0), _ rstCompound.Fields(1) rstCompound.MoveNext Loop Set rstCompound = rstCompound.NextRecordset intCount = intCount + 1 Loop  

I highly recommend leaving as much of the database logic, and the selects, on the database in stored procedures. You'll be giving your DBA more work that's they're really good at, and freeing up the web developer to be doing more creative web stuff.

Michael.