Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SET NOCOUNT ON/OFF

Author  Topic 

mtl777
Yak Posting Veteran

63 Posts

Posted - 2010-07-06 : 14:11:31
I read that it's advisable to use SET NOCOUNT ON at the beginning of a stored procedure. If you do so, is it really necessary to SET NOCOUNT OFF at the end of the stored procedure? I wonder what's the point of doing SET NOCOUNT OFF when the stored procedure is already ending anyway? What happens if you don't SET NOCOUNT OFF?

Thanks in advance for any help!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-07-06 : 14:28:27
It is not necessary to SET NOCOUNT OFF.



CODO ERGO SUM
Go to Top of Page

mtl777
Yak Posting Veteran

63 Posts

Posted - 2010-07-06 : 14:47:04
Thank you so much! Knowing this will save me a lot of time as I have hundreds of stored procedures to modify.
Go to Top of Page

mtl777
Yak Posting Veteran

63 Posts

Posted - 2010-07-20 : 14:12:36
OK, a related question: I am invoking a bunch of stored procedures from a Visual Basic program. If the first stored procedure invoked has SET NOCOUNT ON and does not set it off upon exiting, will the remaining stored procedures inherit the NOCOUNT ON setting so they do not have to individually set it on? Or is the NOCOUNT ON setting automatically reset to the default OFF when the first stored procedure exits? (Note: The connection remains open and is never closed at any time during all of this.)

Thanks!
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-20 : 14:21:06
quote:
Originally posted by mtl777

OK, a related question: I am invoking a bunch of stored procedures from a Visual Basic program. If the first stored procedure invoked has SET NOCOUNT ON and does not set it off upon exiting, will the remaining stored procedures inherit the NOCOUNT ON setting so they do not have to individually set it on? Or is the NOCOUNT ON setting automatically reset to the default OFF when the first stored procedure exits? (Note: The connection remains open and is never closed at any time during all of this.)

Thanks!



Every stored procedure needs SET NOCOUNT ON as they do not inherit the setting from other objects.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

mtl777
Yak Posting Veteran

63 Posts

Posted - 2010-07-20 : 15:49:23
Hi Tara, thanks a lot!
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-20 : 16:33:07
No problem.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
   

- Advertisement -