Use of SET NOCOUNT in Stored Procedures

SET NOCOUNT ON statement into store procedures can reduce network traffic, because client will not receive the message indicating the number of rows affected by T-SQL statement.

When SET NOCOUNT is ON, the count is not returned.
When SET NOCOUNT is OFF, the count is returned.
The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

The setting specified by SET NOCOUNT is in effect at execute or run time and not at parse time.

Example:

USE DEMO

GO

SELECT * FROM [DEMO].[EMP];

Message:

12 row(s) affected

USE DEMO

GO

SET NOCOUNT ON

SELECT * FROM [DEMO].[EMP];

SET NOCOUNT OFF

Message:

Command(s) completed successfully.