SET NOCOUNT ON prevents the sending of progress messages to the client for each statement in a stored procedure or a script. When SQL Server 2005 queries executed, the results prevent [ X rows affected] message from being displayed at the end Transact-SQL statements such as SELECT, INSERT, UPDATE, and DELETE. For some stored procedures or scripts that contain several statements that do not return much actual data, this may provide a significant performance boost because network traffic between the server and client is reduced to a degree. Example below for using SET NOCOUNT ON in a stored procedure and in the execution script.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'Production.GetProductListBelowMaxPrice', 'P' ) IS NOT NULL
DROP PROCEDURE Production.GetProductListBelowMaxPrice;
GO
CREATE PROCEDURE Production.GetProductListBelowMaxPrice
@Product varchar(40)
, @PriceLimit smallmoney
, @Items int OUTPUT
, @ListPrice smallmoney OUT
AS
BEGIN
SET NOCOUNT ON;

SELECT p.name AS ProductName,
'$'+convert(varchar,p.ListPrice,1) AS 'List Price'
FROM Production.Product p
JOIN Production.ProductSubcategory ps
ON p.ProductSubcategoryID = ps.ProductSubcategoryID
WHERE ps.name LIKE @Product AND p.ListPrice < @PriceLimit
ORDER BY [List Price] desc;
SET @Items = @@Rowcount
SET @ListPrice = (SELECT MAX(p.ListPrice)
FROM Production.Product p
JOIN Production.ProductSubcategory ps
ON p.ProductSubcategoryID = ps.ProductSubcategoryID
WHERE ps.name LIKE @Product AND p.ListPrice < @PriceLimit);
RETURN 1
END
GO

/*********** EXECUTION SCRIPT ***************/
SET NOCOUNT ON;
DECLARE @RC int
DECLARE @Product varchar(40)
DECLARE @PriceLimit smallmoney
DECLARE @Items int
DECLARE @ListPrice smallmoney

SET @Product = 'Road%'
SET @PriceLimit = 1000.0

EXECUTE @RC = [AdventureWorks].[Production].[GetProductListBelowMaxPrice]
@Product
,@PriceLimit
,@Items OUTPUT
,@ListPrice OUTPUT

SELECT ReturnFlag = @RC, Items=@Items, LimitPrice=@PriceLimit, MaxPriceReturned=@ListPrice
GO