SQL server set nocount on means:
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. SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure.
In your case, You are looking at the wrong part of SQL Servers's output. NOCOUNT only controls the extra "row(s) affected" messages that are output after SELECT/INSERT/UPDATE/DELETE/MERGE operations, not the rowsets output of any SELECTs that don't have a destination specified. You can see this if you set SSMS to show output as text:
SET NOCOUNT OFF SELECT test='NoCount is OFF' SET NOCOUNT ON SELECT test='NoCount is ON' SET NOCOUNT OFF SELECT test='NoCount is OFF again' -- now lets try with operations that store the rowset instead of sending as output SET NOCOUNT OFF PRINT 'A rows affected count should follow' SELECT test='NoCount is OFF' INTO #temptable SET NOCOUNT ON PRINT 'But there won''t be one for this statement' SELECT test='NoCount is ON' INTO #temptable2 SET NOCOUNT OFF PRINT 'A rows affected count should follow' SELECT test='NoCount is OFF again' INTO #temptable3 test -------------- NoCount is OFF (1 row(s) affected) test ------------- NoCount is ON test -------------------- NoCount is OFF again (1 row(s) affected) A rows affected count should follow (1 row(s) affected) But there won't be one for this statement A rows affected count should follow (1 row(s) affected)
will produce the output: or if you leave SSMS set to show output in grids, you'll get the grids in the results tab and the following in the messages tab: (1 row(s) affected) (1 row(s) affected) A rows affected count should follow (1 row(s) affected) But there won't be one for this statement A rows affected count should follow (1 row(s) affected) There is no way (that I know of) to stop all rowsets being output by a stored procedure that wants to output them. You can use the form INSERT
EXEC to put the output into a temporary table (then just drop the table or let it be dropped as your session ends) to hide the first set of results, but this has a number of significant limitations:
- The table must already exist, you can't do the equivalent of SELECT INTO FROM ..., which adds a chunk of extra code (to create the table)
- As with any other INSERT either the table must have the right number of columns or you need to specify the destination columns, and in either case they must, of course, be of compatible types INSERT
EXEC ... can not be nested, so this will break if the procedure any dependencies it may have also use INSERT
EXEC ...It can only capture one result set, so won't work as you desire if the called procedure returns more than one
Another thing to note about NOCOUNT is that the called stored procedure may override the setting you specify by using SET NOCOUNT {ON|OFF} itself so it might output counts ignoring your setting and you'll need to check/reset it after each call to make sure it is how you want it (see Artashes's answer for how to use @@OPTIONS to check).
