Overview
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. By default, the number of rows affected by a query statement are returned, but this can be turned on and off by using SET NOCOUNT.
Explanation
As mentioned above, there is not really a 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 if neeed to be used in the code, so turning SET NOCOUNT ON will not change that behavior.
SET NOCOUNT Default Behavior
By default SET NOCOUNT is OFF and will return the number of rows affected. Here is an example:
CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)
AS
SELECT *
FROM Person.Address
WHERE City = @City
GOWe could then run the stored procedure as follows:
EXEC dbo.uspGetAddress 'Calgary'If we look at the Messages in SSMS we would see something like this:
(23 row(s) affected)Using SET NOCOUNT ON
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.
CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)
AS
SET NOCOUNT ON
SELECT *
FROM Person.Address
WHERE City = @City
GOWe could run the stored procedure again as follows:
EXEC dbo.uspGetAddress 'Calgary'The Messages tab would be similar to this:
Command(s) completed successfully.Using SET NOCOUNT ON and @@ROWCOUNT
This example uses SET NOCOUNT ON, but will still return the number of rows impacted by the previous statement. This just shows that @@ROWCOUNT will still return a value.
CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)
AS
SET NOCOUNT ON
SELECT *
FROM Person.Address
WHERE City = @City
PRINT @@ROWCOUNT
GOWe could run the stored procedure again as follows:
EXEC dbo.uspGetAddress 'Calgary'The Messages tab would be similar to this:
23SET NOCOUNT OFF
If you wanted to turn this behavior off, you would just use the command “SET NOCOUNT OFF” as follows:
CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)
AS
SET NOCOUNT OFF
SELECT *
FROM Person.Address
WHERE City = @City
PRINT @@ROWCOUNT
GOWe could run the stored procedure again as follows:
EXEC dbo.uspGetAddress 'Calgary'The Messages tab would be similar to this, showing the counts both ways for the rows affected.
(23 rows affected)
23
