0

I have the following stored procedure in my mysql database:

BEGIN 
DECLARE useCount, remainingUses INT DEFAULT 0;

/* Get the current values for the quiz into the variables */
SELECT remaining_uses, use_count INTO remainingUses, useCount FROM quiz_passwords WHERE password_id  = passwordId;

/* Are there remaining uses to consume? */
if (remainingUses > 0) THEN

    UPDATE quiz_passwords SET use_count = (useCount + 1), remaining_uses = (remainingUses - 1) where password_id = passwordId;

END IF;

END

As you can see, the update statement should only be executed if the remainingUses variable from the initial select statement is more than '0'.

However, when I call the procedure CALL UsePassword(197);, it returns Affected rows: 1.

I don't understand, when my password row with id = 197 in the database has a value of 'remaining_uses = 0'.

Is there a reason why if would show Affected rows: 1 in the result?

Is it that if the statement is executed successfully that it returns 1 affected row? Because technically in this example my UPDATE statement is not being executed.

Update Not only is nothing updated, but if I completely remove the update statement it still tells me that there was one affected row!

Thanks

Luke
  • 22,826
  • 31
  • 110
  • 193

1 Answers1

2

In the end I solved the problem by declaring an OUT variable in my stored procedure and then returning the value by setting it's value after the SELECT statement.

/* Return the affected rows */
SET affected_rows = ROW_COUNT();
Luke
  • 22,826
  • 31
  • 110
  • 193