title: ExecuteNonQuery is returning Random numbers instead of returning actual rows
body: <p>I have stored procedure which takes xml as an input .</p>
  
  <p>The C# code to execute the SP is </p>
  
  <pre><code>using (SqlConnection conn = new SqlConnection(connString))
  {
      try
      {
          conn.Open();
          SqlCommand command = new SqlCommand("sp_Configure_Users", conn);
          command.CommandType = CommandType.StoredProcedure;
          var xmlData = new SqlParameter(parameterName: "@XMLDATA", value: GenerateXML(_userDetails));
          command.Parameters.Add(xmlData);
  
          count = command.ExecuteNonQuery();
          if (count &gt; 0)
          {
              return count;
          }
          else
          {
              return 0;
          }
      }
      catch (SqlException  )
      {
  
      }
  }
  </code></pre>
  
  <p>The method GenerateXML returns a string which is in the form of xml data .In my stored proc i have removed SET NOCOUNT ON to return me the actual no of rows when inserted ,deleted or updated .If i run my query with the sample xml data :-</p>
  
  <pre><code>&lt;UserCollection&gt;
      &lt;InsertList&gt;&lt;Users User_Id="438"  First_Name="Praveen"  Middle_Name=""  Last_Name="Kumar"  Designation_Id="1"  Email="" Contact_Number="96533"  Updated_By="pkumar"  /&gt;
      &lt;/InsertList&gt;
      &lt;UpdateList&gt;&lt;/UpdateList&gt;
      &lt;DeleteList&gt;&lt;/DeleteList&gt;
  &lt;/UserCollection&gt;
  </code></pre>
  
  <p>The value returned is 3 instead of 1 as in my collection im passing only a single row  which needs to be inserted .Since there are no rows which are updated or deleted .How can the SP return 3 when it has to return only 1 ??</p>
  
  <p>The stored Proc is </p>
  
  <pre><code>  set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
     go
  
  
  
    ALTER PROCEDURE [dbo].[sp_Configure_Users]
  @XMLDATA xml
    AS
   DECLARE @Users_Staging Table(
  [User_Id] [int] NOT NULL,
  [First_Name] [varchar](200) NOT NULL,
  [Middle_Name] [varchar](200) NULL,
  [Last_Name] [varchar](200) NOT NULL,
  [Designation_Id] [int] NOT NULL,
  [Email] [varchar](250) NOT NULL,
  [Contact_Number] [varchar](50) NOT NULL,
  [Updated_By] [varchar](255) NOT NULL
    )
  
    BEGIN
  
    Set nocount off
    Go    
  
    BEGIN TRY
    BEGIN TRANSACTION
  
  
    DECLARE @CurrDate datetime;
    SELECT @CurrDate = GETDATE();
     -- Insert statements for procedure 
    INSERT INTO Users
    ([User_Id]
    ,[First_Name]
    ,[Middle_Name]
    ,[Last_Name]
    ,[Designation_Id]
    ,[Email]
    ,[Contact_Number]
    ,[Updated_By]
    ,[Updated_Date]
    )
    Select    
  XMLDATA.item.value('@User_Id[1]', 'int') AS User_Id,    
  XMLDATA.item.value('@First_Name[1]', 'varchar(200)') AS First_Name,
  XMLDATA.item.value('@Middle_Name[1]', 'varchar(200)') AS Middle_Name,
  XMLDATA.item.value('@Last_Name[1]', 'varchar(200)') AS Last_Name,
  XMLDATA.item.value('@Designation_Id[1]', 'int') AS Designation_Id,
  XMLDATA.item.value('@Email[1]', 'varchar(250)') AS Email,
  XMLDATA.item.value('@Contact_Number[1]', 'varchar(50)') AS Contact_Number,
  XMLDATA.item.value('@Updated_By[1]', 'varchar(255)') AS Updated_By,
  @CurrDate
    FROM @XMLDATA.nodes('//UserCollection/InsertList/Users') AS XMLDATA(item)
  
    -- Update statements for procedure 
  
    INSERT INTO @Users_Staging
         ([User_Id]
    ,[First_Name]
    ,[Middle_Name]
    ,[Last_Name]
    ,[Designation_Id]
    ,[Email]
    ,[Contact_Number]
    ,[Updated_By])
  
  Select  
  XMLDATA.item.value('@User_Id[1]', 'int') AS User_Id,    
  XMLDATA.item.value('@First_Name[1]', 'varchar(200)') AS First_Name,
  XMLDATA.item.value('@Middle_Name[1]', 'varchar(200)') AS Middle_Name,
  XMLDATA.item.value('@Last_Name[1]', 'varchar(200)') AS Last_Name,
  XMLDATA.item.value('@Designation_Id[1]', 'int') AS Designation_Id,
  XMLDATA.item.value('@Email[1]', 'varchar(250)') AS Email,
     XMLDATA.item.value('@Contact_Number[1]', 'varchar(50)') AS Contact_Number,
  XMLDATA.item.value('@Updated_By[1]', 'varchar(255)') AS Updated_By
    FROM @XMLDATA.nodes('//UserCollection/InsertList/Users') AS XMLDATA(item)
  
  
    UPDATE Users 
    SET First_Name=stgusr.First_Name,
    Middle_Name=stgusr.Middle_Name,
    Last_Name=stgusr.Last_Name,
    Email=stgusr.Email,
    Contact_Number=stgusr.Contact_Number,
    Updated_By=stgusr.Updated_By,
    Updated_Date=@CurrDate
    FROM Users usr INNER JOIN
    @Users_Staging stgusr
    ON usr.User_Id=stgusr.User_Id
  
  -- Delete statements for procedure 
  
     DELETE Users
     WHERE User_Id
       IN (Select         
      XMLDATA.item.value('@User_Id[1]', 'int') AS User_Id
  FROM @XMLDATA.nodes('//UserCollection/DeleteList/Users') AS XMLDATA(item))
  
     COMMIT TRANSACTION;
  
     END TRY
  
     BEGIN CATCH
  --EXEC sp_rethrow_error;
  ROLLBACK TRANSACTION;
      END CATCH; 
     END
  </code></pre>