Retrieving Multiple Recordsets

You might occasionally need to execute a command that will return more than one result set. A common example is a stored procedure that runs against a SQL Server database, as in the following example. The stored procedure contains a COMPUTE clause to return the average price of all products in the table. The definition of the stored procedure is as follows:

CREATE PROCEDURE ProductsWithAvgPrice 
AS
SELECT ProductID, ProductName, UnitPrice 
  FROM PRODUCTS 
  COMPUTE AVG(UnitPrice)

The Microsoft OLE DB Provider for SQL Server returns multiple result sets to ADO when the command contains a COMPUTE clause. Therefore, the ADO code must use the NextRecordset method to access the data in the second result set, as shown here:

'BeginNextRs
    objCmd.CommandText = "ProductsWithAvgPrice"
    objCmd.CommandType = adCmdStoredProc
    
    Set objRs = objCmd.Execute
    
    Do While Not objRs.EOF
        Debug.Print objRs(0) & vbTab & objRs(1) & vbTab & _
                    objRs(2)
        objRs.MoveNext
    Loop
    
    Set objRs = objRs.NextRecordset
    
    Debug.Print "AVG. PRICE = $ " & objRs(0)

    objRs.Close
'EndNextRs

For more information, see NextRecordset.