2

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

14

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

  • thanks, I've completely mistaken the usage of this parameter! – Mark Sep 27 '17 at 9:25
  • 2
    @Mark you might be interested in FMTONLY – AakashM Sep 27 '17 at 10:37
  • @AakashM thanks, FMTONLY is not returning any rowset but it is still returning the columnset...so it is not what i was looking for – Mark Sep 27 '17 at 15:37
2

SET NOCOUNT (Transact-SQL)

Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.

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. 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. The setting specified by SET NOCOUNT is in effect at execute or run time and not at parse time. To view the current setting for this setting, run the following query.

DECLARE @NOCOUNT VARCHAR(3) = 'OFF';  
IF ( (512 & @@OPTIONS) = 512 ) SET @NOCOUNT = 'ON';  
SELECT @NOCOUNT AS NOCOUNT;  

You can read more here

  • Specifically the problem with the DONE_IN_PROC messages is that they are converted by e.g. ADO or ADO.NET into empty, closed result sets which have the count, errors and print messages attached to them. This means you then have to skip over the empty result sets to find your data, but worse, means that a change to the implementation of the proc can result in new empty, closed, ADO resultsets which can break your application if it isn't expecting them. – Ben Sep 27 '17 at 13:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.