Problem when creating and executing stored procedure in mysql

Question

I had created a stored procedure in MySQL. It has to get the values for 5 different columns and perform calculations based on the input params and update the calculated value to the table. I was able to create the stored procedure, but when executing it, it throws an error stating that 'MYSQL said: #1172 - Result consisted of more than one row.'

Below is my stored procedure

DELIMITER $$
CREATE DEFINER=`andrew`@`localhost` PROCEDURE `st_update_userpoints`(
     in point int,
     in gold int,
     in silver int,
     in bronze int,
     in userId int)
BEGIN
     DECLARE _gold int default 0;
     DECLARE _silver int default 0;
     DECLARE _bronze int default 0;
     DECLARE _points int default 0;
     DECLARE _score int default 0;
     
     select `goldMedal`,`silverMedal`,`bronzeMedal`,`totalPoints`,`score` into @_gold,@_silver,@_bronze,@_points,@_score from `userpoints` where `userId`=userId;
     
     set _gold:=(@_gold+gold);
     set _silver:=(@_silver+silver);
     set _bronze:=(@_bronze+bronze);
     set _points:=(@_points+points);
     set _score = (@_gold*5)+(@_silver+3)+(@_bronze)+(@_points);
     
     update `userpoints` set `goldMedal`=@_gold, `silverMedal`=@_silver, `bronzeMedal`=@_bronze, `totalPoints`=@_points,
     `score`=@_score where `userId`=userId;
     END$$
DELIMITER ;

The only statement I can see that can produce this error is: select ... from userpoints` where userId=userId;`. Are you sure this SQL query can only produce 1 row at most? — The Impaler
– The Impaler, Commented Dec 8, 2021 at 4:02

Akina · Accepted Answer · 2021-12-08 05:19:23Z

2

where `userId`=userId uses in userId int input parameter value for both left and right part. Hence it is always TRUE except the rows where userpoints.userId is NULL (and in this case the query returns multiple rows), and it is always NULL==FALSE if the provided parameter value is NULL (and the query does not return rows).

Use where userpoints.userId=userId - in this case the leftpart value is column value and the right part value is provided parameter value. And I'd check the provided value for NULL...

answered Dec 8, 2021 at 5:19

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andrew Rayan · Accepted Answer · 2021-12-08 10:05:07Z

Finally corrected stored procedure

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `st_update_userpoints`(
     in points int,
     in gold int,
     in silver int,
     in bronze int,
     in userId int)
BEGIN
     DECLARE _gold int default 0;
     DECLARE _silver int default 0;
     DECLARE _bronze int default 0;
     DECLARE _points int default 0;
     DECLARE _score int default 0;
     
     select `goldMedal`,`silverMedal`,`bronzeMedal`,`totalPoints`,`score` into _gold,_silver,_bronze,_points,_score from `userpoints` where `userpoints`.`userId`=userId;
     
     set _gold:=(_gold+gold);
     set _silver:=(_silver+silver);
     set _bronze:=(_bronze+bronze);
     set _points:=(_points+points);
     set _score = (_gold*5)+(_silver*3)+(_bronze*1)+(_points);
     
     update `userpoints` set `goldMedal`=_gold, `silverMedal`=_silver, `bronzeMedal`=_bronze, `totalPoints`=_points,
     `score`=_score where `userpoints`.`userId`=userId;
     END $$
DELIMITER ;

Collectives™ on Stack Overflow

Problem when creating and executing stored procedure in mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related