Alison Balter's Mastering Access 2002 Enterprise Development

The SET NOCOUNT Statement

The SET NOCOUNT statement, when set to ON, eliminates the “xx row(s) affected” message in the Query Analyzer window. It also eliminates the DONE_IN_PROC communicated from SQL Server to the client application. For this reason, the SET NOCOUNT ON statement, when included, improves the performance of the stored procedure. Here's an example:

CREATE PROCEDURE procGetCustomersNoCount AS
SET NOCOUNT ON
SELECT CompanyName, ContactName, ContactTitle, City
FROM Customers
ORDER BY City, CompanyName

If you execute this stored procedure from the Query Analyzer, you'll notice that the “xx row(s) affected” message does not appear (see Figure 8.21). You might wonder how with SET NOCOUNT ON you essentially can return the number of ...