Problem when creating and executing stored procedure in mysql-CodePudding

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 ;

CodePudding user response：

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...

CodePudding user response：

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 ;