I have the following stored procedure
DROP PROCEDURE IF EXISTS `p25`$$
CREATE DEFINER=`root`@`%` PROCEDURE `p25`()
BEGIN
DECLARE b BOOLEAN;
DECLARE a VARCHAR(10);
DECLARE cur_1 CURSOR FOR SELECT t FROM sample_table;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = TRUE;
OPEN cur_1;
lbl:LOOP
IF b = TRUE THEN
LEAVE lbl;
END IF;
IF NOT b= TRUE THEN
FETCH cur_1 INTO a;
END IF;
END LOOP;
CLOSE cur_1;
END$$
the select statements actually returns 5 rows but the above procedure after one row is fetched it is not looping through other rows. and the b value is never set to true and it is going to infinite loop because of that.
Is there any problem with my code?
I got the solution for this, thanks for helping me. i think the way i am checking the values is wrong. So now, i want to fetch the values from some other table depending on the row value fetched. how can i print the result like ||current row value || the values fetched from other table|| as a result for all the rows fetched.
Try to change -
IF NOT b= TRUE THEN
FETCH cur_1 INTO a;
with -
IF b IS NULL THEN
FETCH cur_1 INTO a;
Try this code -
DECLARE b INT DEFAULT 0;
DECLARE a VARCHAR(10);
DECLARE cur_1 CURSOR FOR SELECT t FROM sample_table;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 INTO a;
IF NOT b THEN
-- do something:
...
...
...
END IF;
UNTIL b END REPEAT;
CLOSE cur_1;