SET NO COUNT ON – Stored Procedures

For every statement in a stored procedure, SET NOCOUNT ON stops procedure outcome messages from being sent to the client. In other words, setting NOCOUNT to ON will make messages like “number of rows affected” ineffective.

This slideshow requires JavaScript.

Image Credits: Google.

As per the documentation, for stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced. The setting specified by SET NOCOUNT is in effect at execute or run time and not at parse time.

So, finally, how it is supposed to declare?
Here you go.

CREATE PROCEDURE uspGetOrdersInfo
AS
BEGIN
SET NOCOUNT ON

DELETE FROM tbOrders WHERE Status = ‘FAILED’
SELECT * FROM tbOrders WHERE Status = ‘PLACED’

GO;

EXEC uspGetOrdersInfo;

Hope you find this article helpful.