There are many tricks that can be used when you write T-SQL code. One of these is to reduce the amount of network data for each statement that occurs within your stored procedures. Every time a SQL statement is executed it returns the number of rows that were affected. By using "SET NOCOUNT ON" within your stored procedure you can shut off these messages and reduce some of the traffic.
As mentioned above there is not really any reason to return messages about what is occuring within SQL Server when you run a stored procedure. If you are running things from a query window, this may be useful, but most end users that run stored procedures through an application would never see these messages.
You can still use @@ROWCOUNT to get the number of rows impacted by a SQL statement, so turning SET NOCOUNT ON will not change that behavior.
Here is an example without using SET NOCOUNT ON:
-- not using SET NOCOUNT ON CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30) AS SELECT * FROM Person.Address WHERE City = @City GO
The messages that are returned would be similar to this:
This example uses the SET NOCOUNT ON as shown below. It is a good practice to put this at the beginning of the stored procedure.
-- using SET NOCOUNT ON CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30) AS SET NOCOUNT ON SELECT * FROM Person.Address WHERE City = @City GO
The messages that are returned would be similar to this:
This example uses SET NOCOUNT ON, but will still return the number of rows impacted by the previous statement. This just shows that this still works.
-- not using SET NOCOUNT ON CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30) AS SET NOCOUNT ON SELECT * FROM Person.Address WHERE City = @City PRINT @@ROWCOUNT GO
The messages that are returned would be similar to this:
If you wanted to turn this behavior off, you would just use the command "SET NOCOUNT OFF".