The "SET NOCOUNT ON" statement in Stored Procedures is absolutely necessary to avoid some ADO errors, which are very difficult to figure out in programming especially for ASP users.
With the 'SET NOCOUNT OFF' (default) produces messages like "1 row updated", or "3 rows deleted" which fills up the result set (See below for example). So , while working
with result sets, especially in ASP , you will encounter lot of problems running
through the result set.
(1 row(s) affected)
Value
---------------------------------------
1310
The first line (1 row(s) affected) can mess up the result set. We need to get rid of those comments telling us how
many rows were affected. The SET NOCOUNT ON will prevent SQL Server from telling how many rows each statement affected.
SET NOCOUNT OFF will return SQL Server back to its default setting.
In case you need to know the rows that were affected, use @@ROWCOUNT that will return the number of rows that were affected by the SQL statement , regardless
of the setting of NOCOUNT.