是“不取消计数”吗？在存储过程中必要吗？
我有许多程序设置了 nocount on。
是否有必要在存储过程结束时将其关闭？
例如：
create procedure DummyProc
as
begin
set nocount on
...
set nocount off
end
I have many procedures that has 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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
set nocount on将禁用受影响的 X 行。SQL 返回消息。在某些情况下，由于客户端执行存储过程会产生不良影响，此消息会被抑制。set nocount off将撤消此抑制。但是，set nocount on是一个范围设置，默认情况下，无论如何离开范围时都会关闭。现在，有必要
set nocount off吗？不会，因为执行的任何新命令都将在不同的范围内，并且默认情况下set nocount off始终有效。但正如上面评论中所述，这被认为是一个很好的做法，只是为了明确表明当过程执行完成时此设置将恢复正常。set nocount onwill disable theX 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 offwill undo this suppression. However,set nocount onis a scope setting, and by default, will be turned off when leaving the scope anyway.Now, is
set nocount offnecessary? No, as any new commands executed will be in a different scope, and by defaultset 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 ON 创建的作用域会流向您的过程调用的任何过程。因此，如果您的过程执行 SET NOCOUNT ON 然后您调用存储过程，则该存储过程将获取您的 SET NOCOUNT 设置。当您退出存储过程时，该设置会消失，但该设置会流入被调用的存储过程中。如果您在 CALLED 存储过程中设置 SET NOCOUNT，则外部存储过程将具有它设置的 SET NOCOUNT，并且内部存储过程不会影响外部存储过程。
所以我认为你真的不需要在存储过程结束时重置它，因为你的设置永远不会向上流出存储过程；但是，如果您的存储过程取决于该设置，则它应该在需要之前进行设置，因为如果从另一个存储过程调用它，它可能具有与您假设的不同的设置。
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.
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.
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.
不想看到
仅当您大部分时间
时 - 当您调试并使用打印命令时 - 所以您想看到自己的纯文本......所以这是一个很好的做法。
编辑
它不会影响您的查询结果（打开或关闭 - 没关系。） - 如果这就是您的要求。 （感谢 JNK）。
only if you dont want to see
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).
这很简单。如果另一个程序正在运行您的过程，则不需要它。
假设我的 python 代码正在调用 mssql 过程，那么我不需要应用程序上受影响的行计数，所以我“应该”肯定在我的代码中添加“set nocount on”
its simple. you dont need it if another program is running your procedure.
Lets say my python code is calling an mssql procedure, then I don't need the affected row count on my app, so I "SHOULD" definitely put "set nocount on" in my code
我建议在程序结束时将 NOTCOUNT 设置为 OFF 不是一个好习惯。首先，它是没有必要的，因为它是基于范围的，其次，在我们将其设置为 NOCOUNT ON 之前，无法知道它是什么，因此任意将其设置为默认 OFF 实际上可能会产生误导，并且完全没有必要，并使代码。
I would suggest it is not good practice to set the NOTCOUNT to OFF at the end of the procedure. Firstly it is not necessary as it is scope based, and secondly there is no way of telling what it was before we set it to NOCOUNT ON so arbitrarily setting it to the default OFF could actually be misleading as well as being totally unnecessary and bloating the code.