0

I have a SQL Server stored procedure that updates a table.

Here's my stored procedure

CREATE PROCEDURE [dbo].[usp_ExampleUpdate]
    (@iID INT,
     @sCodeName VARCHAR(MAX)
    )
AS
BEGIN
    UPDATE dbo.Example
    SET CodeName = @sCodeName
    WHERE ID = @iID
END

When I execute this stored procedure in SQL Server, it returns "1 row(s) affected", and the data is updated. But when I'm trying to execute this stored procedure from ASP.NET MVC using ExecuteNonQuery, it returns 0.

Here's a sample of my code

public bool UpdateExample(SqlTransaction p_oTrans, int ID, string CodeName)
{
    try
    {
        int iRowAffected = SqlHelper.ExecuteNonQuery(p_oTrans, "usp_ExampleUpdate", ID, CodeName);

         return (iRowAffected > 0);
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

The iRowAffected here always return 0, even though the stored procedure is successfully executed.

I've read the about this, but I still don't get it, why I always get 0.

JTR
  • 333
  • 2
  • 9
  • 31

1 Answers1

0

MSDN:

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

Since you're executing SP and not one of those 3 DML statements, I conclude that it works as designed

T.S.
  • 18,195
  • 11
  • 58
  • 78
  • I've read this so many times, but I still don't get it, can you explain more? – JTR Oct 24 '17 at 04:22
  • Sorry, I can't. You can get JetBrain's "dotPeek" application and look what `ExecuteNonQuery` does inside `System.Data` assembly during SP execution. I am satisfied with MS documentation. This is time-consuming and worthless activity. May be you can get some counts inside SP and return as parameter. – T.S. Oct 24 '17 at 04:27
  • But it looks like the original poster IS using one of those 3 DML statements. It's an Update statement. In addition, you actually want NOCOUNT OFF if you want to return the number of rows affected.SET NOCOUNT OFF; GO Ensure that your stored procedure is actually updating records. – Charles Owen Oct 24 '17 at 04:34
  • @CharlesOwen This is tricky situation in which MS doesn't guarantee return number of rows affected out of SP. However, sometimes, you may get it. – T.S. Oct 24 '17 at 04:43
  • Wait, is that mean the return of 0 value from `ExecuteNonQuery` is valid even the executed from SP has a rows affected (not 0 row(s) affected)? – JTR Oct 24 '17 at 04:47
  • @JTR the consensus is that if you add `SET NOCOUNT OFF` you will get number of rows affected [sometimes]. The bottom line is - MS doesn't guarantee accurate rows affected with `ExecuteNonQuery` and SP. – T.S. Oct 24 '17 at 13:40