Is 'SET NOCOUNT OFF' required?

Normally I bracket my stored procedure code with 'SET NOCOUNT ON' and 'SET
NOCOUNT OFF' statements. Do I actually have to explicitly add the 'SET
NOCOUNT OFF' statement at the end of the Stored procedure or does SQL Server
automatically set it to OFF once the stored procedure is finished executing?
Thanks in advanceAnything SET within the procedure is set only for the duration of the
procedure.
I don't have a single SET NOCOUNT OFF statement throughout our entire
enterprise (but one of our standard coding conventions is to start every
single stored procedure with SET NOCOUNT ON).
"Cipher" <Cipher@.discussions.microsoft.com> wrote in message
news:EEADA5B8-C1F7-422A-A932-7C79A1789A8E@.microsoft.com...
> Normally I bracket my stored procedure code with 'SET NOCOUNT ON' and 'SET
> NOCOUNT OFF' statements. Do I actually have to explicitly add the 'SET
> NOCOUNT OFF' statement at the end of the Stored procedure or does SQL
> Server
> automatically set it to OFF once the stored procedure is finished
> executing?
> Thanks in advance|||It should revert to the server settings after the stored procedure is
completed, so technically, SET NOCOUNT OFF is not required. However,
it's good programming practice to include it.
HTH,
Stu
Cipher wrote:
> Normally I bracket my stored procedure code with 'SET NOCOUNT ON' and 'SET
> NOCOUNT OFF' statements. Do I actually have to explicitly add the 'SET
> NOCOUNT OFF' statement at the end of the Stored procedure or does SQL Serv
er
> automatically set it to OFF once the stored procedure is finished executin
g?
> Thanks in advance|||Always set it ON, don't worry about setting it off.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Cipher" <Cipher@.discussions.microsoft.com> wrote in message
news:EEADA5B8-C1F7-422A-A932-7C79A1789A8E@.microsoft.com...
> Normally I bracket my stored procedure code with 'SET NOCOUNT ON' and 'SET
> NOCOUNT OFF' statements. Do I actually have to explicitly add the 'SET
> NOCOUNT OFF' statement at the end of the Stored procedure or does SQL
> Server
> automatically set it to OFF once the stored procedure is finished
> executing?
> Thanks in advance