Receiving Multiple Recordsets

The Microsoft OLE DB Provider for SQL Server supports returning multiple Recordset objects for a single command containing multiple SQL statements, one Recordset per SQL statement. The order in which the Recordsets are returned follows the order in which the SQL statements are placed in the command text.

The first code example in this section demonstrates this feature by querying two tables, Orders and Customers, in the Northwind database to examine who is the customer whose ID is "ALFKI" and where are the orders shipped.

The Microsoft OLE DB Provider for SQL Server also returns multiple resultsets to ADO when the command contains a COMPUTE clause. For example, a command containing the following SQL statement—

SELECT ProductID, ProductName, UnitPrice 
  FROM PRODUCTS 
  COMPUTE AVG(UnitPrice)

—will return the results in two Recordset objects: one for the rowset of (ProductID, ProductName, UnitPrice), and the other for the average price of all products in the table. You can use the Recordset.NextRecordset method to enumerate the two objects as shown in the second code example in this section.

For more information, see NextRecordset.

This section contains the following topics.

• Example: Multiple Recordset Objects for Multiple SQL Statements in a Single Command
• Example: Multiple Recordset Objects for One SQL statement with a COMPUTE Clause