48

I have many procedures that has set nocount on.

Is it necessary to turn it off at the end of stored procedure?

e.g.:

create procedure DummyProc
as
begin
    set nocount on
    ...
    set nocount off
end
Jerad Rose
  • 15,235
  • 18
  • 82
  • 153
dance2die
  • 35,807
  • 39
  • 131
  • 194
  • 1
    Its good programming practice, yes. – Maess Nov 21 '11 at 19:16
  • 11
    I think the question is more *"how does leaving it `on` affect subsequent SQL"*, isn't it? – Tomalak Nov 21 '11 at 19:20
  • 17
    Side note: no need to reset the set parameter in the end of the procedure. All `set`s done in a procedure only last inside that procedure. – GSerg Nov 21 '11 at 19:24
  • @GSerg - post that please. That's the answer and I'll upvote ya :) – JNK Nov 21 '11 at 19:25
  • So is the question about `set nocount off` or `set nocount on`? My answer was based on the original question, but Adam later changed it -- was this based off some assumptions? Those are two totally different questions. – Jerad Rose Nov 21 '11 at 19:52
  • 1
    FYI, I rolled back Adam's title change, as in the body of the question, it is clear the OP was asking about `set nocount off`. – Jerad Rose Nov 21 '11 at 19:53
  • @MichaelFredrickson - I know that's what BOL says but it isn't true. [test it!](http://stackoverflow.com/questions/7507714/sql-server-2005-transaction-level-and-stored-procedures/7507829#7507829) – Martin Smith Dec 03 '11 at 19:07

4 Answers4

47

set nocount on will disable the X rows affected. message SQL returns. This message is suppressed, in some cases, due to undesired effects with the client executing the stored proc.

set nocount off will undo this suppression. However, set nocount on is a scope setting, and by default, will be turned off when leaving the scope anyway.

Now, is set nocount off necessary? No, as any new commands executed will be in a different scope, and by default set nocount off is always in effect. But as stated above in comments, it's considered a good practice, just to explicitly indicate that this setting will return to normal when the proc is finished executing.

Jerad Rose
  • 15,235
  • 18
  • 82
  • 153
  • 5
    Interestingly there is no 'set nocount off' in the stored procedure templates which is generated in SQL Management Studio by selecting 'Create new Stored Procedure' – Mladen Mihajlovic Mar 23 '14 at 12:32
  • Regarding the undesired effects with clients, one such example is the JDBC driver - the row counts interfere with retrieving the result set(s) via JDBC. – Brandon Jul 14 '14 at 14:46
  • 2
    It's better practice to spread good knowledge of scoping. Otherwise, what would be the "right" way to interpret/write this: `set nocount on; ..; if x = 1 return 1; set nocount off`? There is no good way without understanding the scoping behavior and applying it correctly. – user2864740 Sep 26 '17 at 20:55
11

I know this is a rather old post but it was the first hit on Google when I looked for the answer. The response above to test it was a very good idea.

I tested this out and wanted to update the above with some additional details.

The scope you create with a SET NOCOUNT ON flows to any procs which your procedure calls. So if your procedure does SET NOCOUNT ON and then you call a sproc, that sproc gets your SET NOCOUNT setting. The setting DOES go away when you exit your sproc but the setting flows down into called sprocs. If you SET NOCOUNT inside of the CALLED sproc, the outer sproc will have the SET NOCOUNT which it set and the inner sproc won't affect the outer sproc.

So I think you don't really need to reset it at the end of your sproc because your settings will never flow OUT of your sproc upwards; however, if your sproc depends on the setting, it should set it before it needs it because if it gets called from another sproc, it could have a different setting than you assume.

John Mitchell
  • 153
  • 1
  • 9
2

only if you dont want to see 

(1 row(s) affected) // or n rows....

most of the time - when you debug

and you use print command - so you want to see pure text of your own... so thats a good practice.

edit

it does Not affect your query result (on or off - it doesn't matter.)- if thats what's your asking. ( thanks JNK).

Royi Namir
  • 144,742
  • 138
  • 468
  • 792
  • 2
    -1 - Doesn't answer the question. He's asking "Will this affect other SQL run after the stored proc", not "what does nocount do?" – JNK Nov 21 '11 at 19:22
  • 10
    Still doesn't address what he's asking though. He wants to know if NOT turning it back on will affect anything run after the proc. – JNK Nov 21 '11 at 19:24
1

its simple. you dont need it if another program is running your procedure. Lets say my python code is calling an mssql procedure, then I don't need the affected row count on my app, so I "SHOULD" definitely put "set nocount on" in my code

Emre
  • 77
  • 1
  • 3