SQL 存储过程中是否需要“SET NOCOUNT OFF”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8217200/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is "SET NOCOUNT OFF" necessary in a stored procedure?
提问by dance2die
I have many procedures that has set nocount on.
我有很多程序都有set nocount on。
Is it necessary to turn it off at the end of stored procedure?
是否有必要在存储过程结束时将其关闭？
e.g.:
例如：
create procedure DummyProc
as
begin
set nocount on
...
set nocount off
end
回答by Jerad Rose
set nocount onwill disable the X rows affected.message SQL returns. This message is suppressed, in some cases, due to undesired effects with the client executing the stored proc.
set nocount on将禁用X rows affected.消息 SQL 返回。在某些情况下，由于客户端执行存储过程的不良影响，此消息被抑制。
set nocount offwill undo this suppression. However, set nocount onis a scope setting, and by default, will be turned off when leaving the scope anyway.
set nocount off将取消这种压制。但是，set nocount on是一个范围设置，默认情况下，无论如何离开范围时都会关闭。
Now, is set nocount offnecessary? No, as any new commands executed will be in a different scope, and by default set nocount offis always in effect. But as stated above in comments, it's considered a good practice, just to explicitly indicate that this setting will return to normal when the proc is finished executing.
现在，有set nocount off必要吗？不，因为执行的任何新命令都将在不同的范围内，并且默认情况下set nocount off始终有效。但正如上面评论中所述，这被认为是一种很好的做法，只是为了明确表明当 proc 执行完成时此设置将恢复正常。
回答by John Mitchell
I know this is a rather old post but it was the first hit on Google when I looked for the answer. The response above to test it was a very good idea.
我知道这是一篇相当古老的帖子，但它是我在 Google 上寻找答案时的第一次点击。上面测试它的响应是一个非常好的主意。
I tested this out and wanted to update the above with some additional details.
我对此进行了测试，并想用一些额外的细节来更新上面的内容。
The scope you create with a SET NOCOUNT ON flows to any procs which your procedure calls. So if your procedure does SET NOCOUNT ON and then you call a sproc, that sproc gets your SET NOCOUNT setting. The setting DOES go away when you exit your sproc but the setting flows down into called sprocs. If you SET NOCOUNT inside of the CALLED sproc, the outer sproc will have the SET NOCOUNT which it set and the inner sproc won't affect the outer sproc.
您使用 SET NOCOUNT ON 创建的范围流向您的过程调用的任何过程。因此，如果您的过程执行 SET NOCOUNT ON，然后您调用 sproc，则该 sproc 将获取您的 SET NOCOUNT 设置。当您退出 sproc 时，该设置会消失，但该设置会向下流入被调用的 sproc。如果您在 CALLED sproc 内部设置 NOCOUNT，则外部 sproc 将具有它设置的 SET NOCOUNT，而内部 sproc 不会影响外部 sproc。
So I think you don't really need to reset it at the end of your sproc because your settings will never flow OUT of your sproc upwards; however, if your sproc depends on the setting, it should set it before it needs it because if it gets called from another sproc, it could have a different setting than you assume.
所以我认为你真的不需要在你的 sproc 结束时重置它，因为你的设置永远不会向上流出你的 sproc；但是，如果您的 sproc 取决于该设置，则它应该在需要之前进行设置，因为如果它被另一个 sproc 调用，它的设置可能与您假设的不同。
回答by Royi Namir
only if you dont want to see
只有当你不想看到
(1 row(s) affected) // or n rows....
most of the time - when you debug
大多数时候 - 当你调试时
and you use print command - so you want to see pure text of your own... so thats a good practice.
并且您使用打印命令 - 所以您想查看您自己的纯文本...所以这是一个很好的做法。
edit
编辑
it does Not affect your query result (on or off - it doesn't matter.)- if thats what's your asking. ( thanks JNK).
它不会影响您的查询结果（打开或关闭 - 没关系。） - 如果这就是您的要求。（感谢 JNK）。
