3

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.

Improve this question
1
  • Check whether you have executed latest sp script on server. Commented May 31, 2012 at 10:25

3 Answers 3

Reset to default
7

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$$
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

stackoverflow.com/questions/10832827/…
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

Improve this answer

Comments

1

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.