Commands Generating Multiple-Rowset Results

SQLOLEDB can return multiple rowsets from SQL Server statements. SQL Server statements return multiple-rowset results under the following conditions:

• Batched SQL statements submitted as a single command.
  
  
• Stored procedures that implement a batch of SQL statements.
  
  
• SQL statements that include the Transact-SQL COMPUTE or COMPUTE BY clause.

Batches

SQLOLEDB recognizes the semicolon character as a batch delimiter for SQL statements:

WCHAR*   wSQLString = L"SELECT * FROM Categories; "
          L"SELECT * FROM Products";
  

Sending multiple SQL statements in one batch is more efficient than executing each SQL statement separately. Sending one batch reduces the network roundtrips from the client to the server.

Stored Procedures

SQL Server returns a result set for each statement in a stored procedure, so most SQL Server stored procedures return multiple result sets.

COMPUTE BY and COMPUTE

The Transact-SQL COMPUTE BY clause generates subtotals within a SELECT statement result set. The COMPUTE clause generates a total at the end of the result set. SQLOLEDB returns each COMPUTE BY subtotal and the COMPUTE total as a separate rowset result.

Using IMultipleResults

In general, consumers should use the IMultipleResults interface to process the rowset or rowsets returned by SQLOLEDB command execution.

When SQLOLEDB submits a command for execution, SQL Server executes the statement or statements and returns any results. The complete process is a roundtrip from the client to the SQL Server and back. Each client connection to a SQL Server can have at most one active roundtrip. That is, within a SQLOLEDB session, only a single command object can be actively executing or returning results on the connection. This is the default result set behavior of SQL Server client connections.

To complete a roundtrip, a client must process all results from command execution. Because SQLOLEDB command execution can generate multiple-rowset objects as results, use the IMultipleResults interface to ensure that application data retrieval completes the client-initiated roundtrip.

The following Transact-SQL statement generates multiple rowsets, some containing row data from the OrderDetails table and some containing results of the COMPUTE BY clause:

SELECT OrderID, FullPrice = (UnitPrice * Quantity), Discount,
  Discounted = UnitPrice * (1 - Discount) * Quantity
FROM OrderDetails
ORDER BY OrderID
COMPUTE
  SUM(UnitPrice * Quantity), SUM(UnitPrice * (1 - Discount) * Quantity)
  BY OrderID
  

If a consumer executes a command containing this text and requests a rowset as the returned results interface, only the first set of rows is returned. The consumer may process all rows in the rowset returned, but until the command is canceled no other commands can be executed on the session object. The consumer can cancel the command either using ICommand::Cancel or by releasing all references held on the command object and the derived rowset.

Using IMultipleResults in all instances allows the consumer to get all rowsets generated by command execution and allows consumers to intelligently determine when to cancel command execution and free a session object for use by other commands.

Note   When using SQL Server cursors, command execution creates the cursor. SQL Server returns success or failure on the cursor creation; therefore, the roundtrip to the SQL Server is complete upon the return from command execution. Each GetNextRows call then becomes a roundtrip. In this way, multiple active command objects can exist, each processing a rowset that is the result of a fetch from the server cursor. For more information, see "Rowsets and SQL Server Cursors."