Is "SET NOCOUNT OFF" necessary in a stored procedure?

Question

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 

Answer 1

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.

Answer 2

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.