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.
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 |
|
|
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. |
|
|
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! |
|
|
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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
mtl777
Yak Posting Veteran
63 Posts |
Posted - 2010-07-20 : 15:49:23
|
Hi Tara, thanks a lot! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|