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