SELECT statements return result sets. UPDATE, INSERT, and DELETE statements return a count of affected rows. If any of these statements are batched, submitted with arrays of parameters (numbered in increasing parameter order, in the order that they appear in the batch), or in procedures, they can return multiple result sets or row counts. For information about batches of statements and arrays of parameters, see Batches of SQL Statements and Arrays of Parameter Values.
After executing the batch, the application is positioned on the first result set. The application can call SQLBindCol, SQLBulkOperations, SQLFetch, SQLGetData, SQLFetchScroll, SQLSetPos, and all the metadata functions, on the first or any subsequent result sets, just as it would if there were just a single result set. Once it is done with the first result set, the application calls SQLMoreResults to move to the next result set. If another result set or count is available, SQLMoreResults returns SQL_SUCCESS and initializes the result set or count for additional processing. If any row count–generating statements appear in between result set–generating statements, they can be stepped over by calling SQLMoreResults.After calling SQLMoreResults for UPDATE, INSERT, or DELETE statements, an application can call SQLRowCount.
If there was a current result set with unfetched rows, SQLMoreResults discards that result set and makes the next result set or count available. If all results have been processed, SQLMoreResults returns SQL_NO_DATA. For some drivers, output parameters and return values are not available until all result sets and row counts have been processed. For such drivers, output parameters and return values become available when SQLMoreResults returns SQL_NO_DATA.
Any bindings that were established for the previous result set still remain valid. If the column structures are different for this result set, then calling SQLFetch or SQLFetchScroll may result in an error or truncation. To prevent this, the application has to call SQLBindCol to explicitly rebind as appropriate (or do so by setting descriptor fields). Alternatively, the application can call SQLFreeStmt with an Option of SQL_UNBIND to unbind all the column buffers.
The values of statement attributes, such as cursor type, cursor concurrency, keyset size, or maximum length, may change as the application navigates through the batch by calls to SQLMoreResults. If this happens, SQLMoreResults will return SQL_SUCCESS_WITH_INFO and SQLSTATE 01S02 (Option value has changed).
Calling SQLCloseCursor, or SQLFreeStmt with an Option of SQL_CLOSE, discards all the result sets and row counts that were available as a result of the execution of the batch. The statement handle returns to either the allocated or prepared state. Calling SQLCancel to cancel an asynchronously executing function when a batch has been executed and the statement handle is in the executed, cursor-positioned, or asynchronous state results in all the results sets and row counts generated by the batch being discarded if the cancel call was successful. The statement then returns to the prepared or allocated state.
If a batch of statements or a procedure mixes other SQL statements with SELECT, UPDATE, INSERT, and DELETE statements, these other statements do not affect SQLMoreResults.
For more information, see Multiple Results.
If a searched update or delete statement in a batch of statements does not affect any rows at the data source, SQLMoreResults returns SQL_SUCCESS. This is different from the case of a searched update or delete statement that is executed through SQLExecDirect, SQLExecute, or SQLParamData, which returns SQL_NO_DATA if it does not affect any rows at the data source. If an application calls SQLRowCount to retrieve the row count after a call to SQLMoreResults has not affected any rows, SQLRowCount will return SQL_NO_DATA.
For additional information about the valid sequencing of result-processing functions, see Appendix B: ODBC State Transition Tables.