MSDN states
SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. 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.
Is reduced network traffic the only reason to use NOCOUNT ON, or does it do anything else?
You wouldn't get the number of rows affected back from the proc.
I think this question and answer, and the links contained with in, especially Remus's about performance, would help out.
It helps some developers in some situations, and DBA's especially when debugging.
In older API's (ADO, before ADO.Net), it would get confused about what was being returned from the proc. It would think the X row(s) affected was the resultset that you were looking for. It would return that instead of a whatever you were selecting.
It doesn't seem to be a problem anymore though.
There are various places where you dont want any output from a stored proc. For example a stored proc that uses temp tables and then a final select would show the number of rows affected for each part, and this can muck up whatever calls the stored proc if its relying on the return from stored proc. As other answers have said this can be a problem with some calling libraries.
Also definitely an issue where you have a loop, as you might end up producing thousands of counts for certain (badly written) procedures.
So in answer, you might find you need to turn on NOCOUNT, or you might need to set NOCOUNT ON for most of the routine, but turn it off just before your final query, or you might be able to just leave it alone.
Most of the time its not an issue, but just be aware of it as a possible source of problems if things are not working.
