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
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 ...

Get Alison Balter's Mastering Access 2002 Enterprise Development now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.