SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SET NOCOUNT ON/OFF
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mtl777
Yak Posting Veteran

63 Posts

Posted - 07/06/2010 :  14:11:31  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 07/06/2010 :  14:28:27  Show Profile  Reply with Quote
It is not necessary to SET NOCOUNT OFF.



CODO ERGO SUM
Go to Top of Page

mtl777
Yak Posting Veteran

63 Posts

Posted - 07/06/2010 :  14:47:04  Show Profile  Reply with Quote
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 - 07/20/2010 :  14:12:36  Show Profile  Reply with Quote
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

USA
37157 Posts

Posted - 07/20/2010 :  14:21:06  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 07/20/2010 :  15:49:23  Show Profile  Reply with Quote
Hi Tara, thanks a lot!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 07/20/2010 :  16:33:07  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000