Is 'SET NOCOUNT OFF' required?

29 Jun 2006 6:30 PM

Cipher

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

29 Jun 2006 6:33 PM

Aaron Bertrand [SQL Server MVP]

Anything 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).

Show quote

"Cipher" <Cip***@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

29 Jun 2006 6:37 PM

Stu

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:
Show quote

> 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

29 Jun 2006 7:08 PM

Arnie Rowland

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

Show quote

"Cipher" <Cip***@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