Ado Examples and Best Practices

SET NOCOUNT is used to toggle the SQL Server Connection option that determines whether a result set is created when you execute an action query, such as INSERT, UPDATE, or DELETE. This result set contains the rows affected value. You can improve performance by eliminating unneeded resultsets, and SET NOCOUNT ON can accomplish this. The SQL Server ODBC driver does not provide information about the results of individual SQL statements within a stored procedure. The only result that comes back from a stored procedure execution is the results of the SELECT statement, if it has one.

SQLOLEDB has a new behavior, designed to provide more accurate information to the caller about what happened in the procedure. Each SQL statement within a stored procedure returns a result set, whether that is a count of rows affected a row set, OUTPUT parameters or any of about a dozen other structures. You can walk through these result sets in ADO using the NextRecordset method on the Recordset object. To get SQLOLEDB to behave as the SQL ODBC driver did, you can use SET NOCOUNT ON in the beginning of the stored procedure.