title: ExecuteNonQuery() and SET NOCOUNT ON
body: <p>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:</p>
  
  <pre><code>…
  DbCommand cmd = db.GetStoredProcCommand("TheStoredProc");
  int numberOfAffectedRows = db.ExecuteNonQuery(cmd);
  …
  </code></pre>
  
  <p>The stored proc TheStoredProc in the DB looks like the following:</p>
  
  <pre><code>PROCEDURE TheStoredProc 
  as
  
      declare @Var1 int
  
      **SET NOCOUNT ON;**
  …
  insert into [Workflows]
      (
          WorkflowInstanceID,
          Status
      )
      select WorkflowInstanceID, 1
          from #tmp
  
  
      DROP TABLE #tmp
  </code></pre>
  
  <p>This is inherited code;  I am not its author. But from the context I see – <strong>the Client expects the ExecuteNonQuery to bring the count of rows inserted by the “insert into [Workflows] …” operator.</strong> </p>
  
  <p>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]…”.</p>
  
  <p>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.</p>
  
  <p>I have the following version: the reason of the bug is the “SET NOCOUNT ON” inb the begin of TheStoredProc. <a href="https://social.msdn.microsoft.com/Forums/en-US/e85ad5e5-a30e-4ccf-aeab-0774185e7576/set-nocount-on-and-sqlcommandexecutenonquery-and-sqldataadapterupdate?forum=adodotnetdataproviders" rel="nofollow">Here</a> I have found the same problem:</p>
  
  <blockquote>
    <p>… 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. …</p>
  </blockquote>
  
  <p>More often (on other forums) I met the complaint that (-1) was returned by ExecuteNonQuery in this situation.</p>
  
  <p>What do you think – am I right?</p>
  
  <p>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….</p>