In MySql Stored Procedure updating more than one time

Question

In MySql

UPDATE `inventoryentry` SET `Status` = 1 WHERE `InventoryID`=92 AND `ItemID`=28;

It successfully update only one row , where inventoryID = 92 and itemID=28 , the following message displayed.

1 row(s) affected 

when I put this on stored procedure, as follow

CREATE DEFINER=`root`@`localhost` PROCEDURE `Sample`(IN itemId INT, IN itemQnty 
DOUBLE, IN invID INT)

BEGIN

DECLARE crntQnty DOUBLE;
DECLARE nwQnty DOUBLE;

SET crntQnty=(SELECT `QuantityOnHand` FROM `item` WHERE id=itemId);
SET nwQnty=itemQnty+crntQnty;
UPDATE `item` SET `QuantityOnHand`=nwQnty WHERE `Id`=itemId;

UPDATE `inventoryentry` SET `Status` = 1 WHERE `InventoryID`=invID AND 
`ItemID`=itemId;


END$$

calling stored procedures

CALL Sample(28,10,92)

It update all the status = 1 in inventoryentry against InventoryID (i.e. 92) ignoring ItemID, instead of updating only one row. The following message displayed!

5 row(s) affected 

Why Stored procedure ignoring itemID in update statement ? or Why Stored procedure updating more than one time? But without Stored procedure it working fine.

Solution 1

You need to use different variable names apart from your field name, also use the table name with the columns for better understanding like i used in following:

CREATE DEFINER=`root`@`localhost` PROCEDURE `Sample`(IN itemID INT, IN itemQnty 
DOUBLE, IN invID INT)

BEGIN

DECLARE crntQnty DOUBLE;
DECLARE nwQnty DOUBLE;

SET crntQnty=(SELECT `QuantityOnHand` FROM `item` WHERE id=itemID);
SET nwQnty=itemQnty+crntQnty;
UPDATE `item` SET `QuantityOnHand`=nwQnty WHERE `QuantityOnHand`.`Id`=itemID;

UPDATE `inventoryentry` SET `Status` = 1 WHERE `InventoryID`=invID AND 
`inventoryentry`.`ItemID`=itemID;


END$$

Solution 2

because of

update inventoryentry ... WHERE ... AND `ItemID`=itemId

You are saying that column itemid should be the same as column itemid which is always true

Try renaming your parameter to a name that differs from your column name

Solution 3

Using same names for columns and variable names has some issues.

Semantics of Stored procedure code is not checked at CREATE time. At runtime, undeclared variables are detected, and an error message is generated for each reference to an undeclared variable. However, SP's seem to believe any reference denotes a column, even though the syntactic context excludes that. This leads to a very confusing error message in case the procedure.

Your column name ItemID matches with input variable name itemId, and hence is the issue.

Please look at my answer to a similar query here.