How I do know if SQL Server Stored Procedure that performs an Update worked?

Question

Say I have this stored procedure that I have no control over (and no access to the third party db).

How do I know if it worked?

BEGIN
        Update USR
        Set usr_psswrd = @NewPassword
        where
            usr_usrnme = @UserName and usr_psswrd = @OldPassword
END

I know how to get rows when there's a select statement in a stored procedure and read those rows but I have no idea how to check if this stored procedure worked or not.

This is what I'm doing so far that doesn't work. The stored procedure works because the password does change I just don't know what to do after the fact.

using (SqlConnection connection = new SqlConnection(connectionString))
{
            // Create the command and set its properties.
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandText = "USP_ChangePassword";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add("@UserName", SqlDbType.VarChar).Value = email;
            command.Parameters.Add("@OldPassword", SqlDbType.VarChar).Value = oldPW;
            command.Parameters.Add("@NewPassword", SqlDbType.VarChar).Value = newPW;

            try
            {
                // Open the connection and execute the reader.
                connection.Open();
                command.ExecuteNonQuery();

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    success = true;
                }

                reader.Close();

            }
            catch (SqlException ex)
            {
                System.Diagnostics.Debug.Write("SqlException Error " + ex.Number + ": " + ex.Message);
            }
            catch (InvalidOperationException ex)
            {
                System.Diagnostics.Debug.Write("Invalid Op Error: " + ex.Message);
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Write("Error: " + ex.Message);
            }
            finally
            {
                connection.Close();
            }
}

Answer 1

IN the documentation about ExecuteNonQuery you could find

> Return Value 
> Type: System.Int32 
> The number of rows affected.

So you could change your code to

try
{
    // Open the connection and execute the reader.
    connection.Open();
    int rowsUpdated = command.ExecuteNonQuery();
    if(rowsUpdated > 0)
    {
        success = true;
    }
}

This is the normal behavior of ExecuteNonQuery, but check if your stored procedure contains the statements

SET NOCOUNT ON

if you have this line, then ExecuteNonQuery cannot return the number of rows affected and you get always a -1 as return value. If you cannot change that stored procedure, then you are in trouble.

The only workaround that comes to mind is to get back the user data with a SELECT query and check against the inserted data (a very uncomfortable situation)

Answer 2

If you check the doco for the ExecuteNonQuery() method you'll see it returns an int, specifically:

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.

Therefore if you execute the method call and it returns a 1 then it is safe to assume that your field has been updated successfully. This means you should also remove the SqlDataReader reader = command.ExecuteReader(); line, and add a return value check something like:

var success = command.ExecuteNonQuery() == 1; 

(that's a compact way of doing it, you could split it out into multiple lines).