ExecuteNonQuery（）和SET NOCOUNT ON [英] ExecuteNonQuery() and SET NOCOUNT ON

问题描述

我使用MS SQL Server 2008 R2
我有一个C＃应用程序–它的客户端。
我遇到了以下错误（但仍然无法重现。以下客户端代码带来的受影响的行数超出了应有的水平：

I use MS SQL Server 2008 R2 I have a C# app – its client. I have met the following bug (and still cannot reproduce it. The following client code brings the "count of rows affected" more than it should:

…
DbCommand cmd = db.GetStoredProcCommand("TheStoredProc");
int numberOfAffectedRows = db.ExecuteNonQuery(cmd);
…

数据库中存储的过程TheStoredProc如下所示：

The stored proc TheStoredProc in the DB looks like the following:

PROCEDURE TheStoredProc 
as

    declare @Var1 int

    **SET NOCOUNT ON;**
…
insert into [Workflows]
    (
        WorkflowInstanceID,
        Status
    )
    select WorkflowInstanceID, 1
        from #tmp


    DROP TABLE #tmp

这是继承的代码；我不是它的作者。但是从上下文中我可以看到– 客户端希望ExecuteNonQuery带来插入[Workflows]…运算符所插入的行数。

This is inherited code; I am not its author. But from the context I see – the Client expects the ExecuteNonQuery to bring the count of rows inserted by the "insert into [Workflows] …" operator.

仅由该运算符INSERT即可。 proc在此之前包含一个INSERT；但是客户端只需要用插入[工作流]…中插入的行数来填充numberOfAffectedRows。

And by only this operator INSERT. The proc contains one more INSERT before that one; but Client needs numberOfAffectedRows to be filled with count of only rows inserted by the "insert into [Workflows]…".

错误如下：numberOfAffectedRows的值为464但是，从数据库内容来看，我应该是419。而且该错误是不可复制的-到目前为止，我在Productions服务器上只看到过一次；仍然无法在我的测试服务器上复制它。在测试服务器上，numberOfAffectedRows是正确的。

The bug is the following: the numberOfAffectedRows got value 464. But from the DB content I see it should have been 419. And the bug is not reproducible – to the moment I saw it only once on the Productions erver; and still cannot reproduce it on my test server. On test server the numberOfAffectedRows is correct.

我有以下版本：该错误的原因是TheStoredProc开头的 SET NOCOUNT ON。 这里我发现了相同的问题：

I have the following version: the reason of the bug is the "SET NOCOUNT ON" inb the begin of TheStoredProc. Here I have found the same problem:

…我听说使用的副作用很少设置为开。
如果存储过程
的SET NOCOUNT ON已打开，则SQLCommand.ExecuteNonQuery函数返回错误的行数。 …

… I have heard few side effects of using "SET NOCOUNT ON" a. SQLCommand.ExecuteNonQuery function returning wrong number of rows if the stored procedure has SET NOCOUNT ON. …

在其他情况下，我更经常抱怨ExecuteNonQuery返回了（-1）。

More often (on other forums) I met the complaint that (-1) was returned by ExecuteNonQuery in this situation.

您怎么看–对吗？

错误修正应为以下内容：插入 SET NOCOUNT OFF在插入[工作流程] ...之前。在我的测试服务器上，这可以正常工作-但初始代码在测试服务器上也可以工作...。

The bugfix should be the following: to insert "SET NOCOUNT OFF" right before the "insert into [Workflows]…". On my test server this works OK – but the initial code works OK too on the test server….

推荐答案

我认为使SET NOCOUNT ON始终会返回-1（在某些情况下可能不正确，但是...）

I would think that having SET NOCOUNT ON would always return -1 (there may be cases where that isn't true, but...)

ExecuteNonQuery会计算受该影响的行数插入/更新，还包括由于触发器而修改的任何行。在测试或生产数据库中的受影响表上是否有任何触发器，它们的行为可能有所不同？

ExecuteNonQuery counts the number of rows affected by the insert/update, but also any rows modified due to triggers. Do you have any triggers on the affected tables in the test or production databases that might behave differently?

是否有任何原因使您不想显式返回计数您感兴趣的行数？例如，在您感兴趣的插入项后更改添加：

Is there any reason why you wouldn't want to explicitly return the count of rows you're interested in? For example, change add this after the insert you're interested in:

select @rowcount = @@ROWCOUNT

然后返回该@rowcount值，或将其传递回输出参数。好像这样做可以使您适应将来的需要，以防万一稍后添加触发器，使触发器的计数发生变化，而又不会实质性影响系统的功能。

and then either return that @rowcount value, or pass it back in an output parameter. Seems like doing this would future-proof you, in case a trigger is added later that causes your counts to change without materially affecting the functionality of your system.

这篇关于ExecuteNonQuery（）和SET NOCOUNT ON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！