title: What could go wrong in this MySQL procedure?
body: <p>I have created procedure in MySQL to maintain stock. Here how it works if user want to maintain stock closing and sold is updated else only sold is updated, This is MySQL Procedure for maintaining stock, which does not worked, returned 0 rows affected, I have checked all data is available in tables for this to work but still not working:</p>
  
  <pre><code>BEGIN
  WHILE (qqty &gt; 0) DO 
  
  IF (maintainStock = 1) THEN
      SET @date1 = (SELECT MIN(expiry_date) FROM `stock_current` WHERE closing &gt; 0);
      UPDATE `stock_current` SET sold = sold + 1, closing = closing - 1 WHERE item_id = iid AND expiry_date = @date1;
      SET qqty = qqty - 1;
  
  ELSE
      SET @date2 = (SELECT MIN(expiry_date) FROM `stock_current`);
      UPDATE `stock_current` SET sold = sold + 1 WHERE item_id = iid AND expiry_date = @date2;
      SET qqty = qqty - 1;
  
  END IF;
  END WHILE;
  END
  </code></pre>