SQL Server – SET NOCOUNT ON Still Returning Data

sql serversql-server-2016

Following the example from this page, I'm unable to stop a stored procedure from returning a select dataset.

Executing this:

SET NOCOUNT OFF;
GO
-- Display the count message.
SELECT 'should be displayed'
GO
-- SET NOCOUNT to ON to no longer display the count message.
SET NOCOUNT ON;
GO
SELECT 'should NOT be displayed'
GO
-- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO

Will return me this:

SET NOCOUNT ON ISSUE

Is this a bug? Is there something wrong? I'm on a SQL Server 13.0.4206

Best Answer

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

will produce the output:

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)

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 <table> EXEC <procedure> <params> 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:

  1. The table must already exist, you can't do the equivalent of SELECT <stuff> INTO <newtable> FROM ..., which adds a chunk of extra code (to create the table)

  2. 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

  3. INSERT <table> EXEC ... can not be nested, so this will break if the procedure any dependencies it may have also use INSERT <table> EXEC ...

  4. 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).

Related Question