Mysql nested loop in stored procedure

Question

I am currently working on one stored procedure in which I use one while loop in another while loop. but I do not get the expected result. the outermost loop is iterating just once.

I am trying to make the following code.

DELIMITER $$ DROP PROCEDURE IF EXISTS `First_Sp` $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `First_Sp`() BEGIN DECLARE first_while_start INTEGER DEFAULT 1; DECLARE second_while_start INTEGER DEFAULT 1; DECLARE first_while_count INTEGER DEFAULT 3; DECLARE second_while_count INTEGER DEFAULT 3; WHILE first_while_start < first_while_count DO WHILE second_while_start < second_while_count DO SELECT concat(first_while_start,' - ',second_while_start) as result; SET second_while_start = second_while_start + 1; END WHILE; SET first_while_start = first_while_start + 1; END WHILE; END $$ DELIMITER ; 

Result:

 mysql> call first_sp(); +--------+ | result | +--------+ | 1 - 1 | +--------+ 1 row in set (0.00 sec) +--------+ | result | +--------+ | 1 - 2 | +--------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec). 

I also tried a repeat loop. but still no LUCK.

 DELIMITER $$ DROP PROCEDURE IF EXISTS `First_Sp` $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `First_Sp`() BEGIN DECLARE first_while_start INTEGER DEFAULT 1; DECLARE second_while_start INTEGER DEFAULT 1; DECLARE first_while_count INTEGER DEFAULT 3; DECLARE second_while_count INTEGER DEFAULT 3; REPEAT WHILE second_while_start < second_while_count DO SELECT concat(first_while_start,' - ',second_while_start) as result; SET second_while_start = second_while_start + 1; END WHILE; SET first_while_start = first_while_start + 1; UNTIL first_while_start < first_while_count END REPEAT; END $$ DELIMITER ; 

I am not very good at SQL Developer. I'm trying to.

Answer 1

This is because after the first iteration of the outer while loop, the value of second_while_start already second_while_count , so the inner loop is not executed, since second_while_start not less than second_while_count .

To get the "expected result", you need to reset second_while_start.

 DELIMITER $$ DROP PROCEDURE IF EXISTS `First_Sp` $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `First_Sp`() BEGIN DECLARE first_while_start INTEGER DEFAULT 1; DECLARE second_while_start INTEGER DEFAULT 1; DECLARE first_while_count INTEGER DEFAULT 3; DECLARE second_while_count INTEGER DEFAULT 3; WHILE first_while_start < first_while_count DO WHILE second_while_start < second_while_count DO SELECT concat(first_while_start,' - ',second_while_start) as result; SET second_while_start = second_while_start + 1; END WHILE; SET first_while_start = first_while_start + 1; /*here comes the important line:*/ SET second_while_start = 1; END WHILE; END $$ DELIMITER ;