currently i am working on one Stored Procedure in which i am using one while loop under another while loop. but i am not getting expected result. the most outer loop is iteration just one time.
I am trying 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 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 much of SQL Developer. I am trying.
This is because after the first iteration of the outer while loop the value of second_while_start
is already second_while_count
thus the inner loop does not execute as second_while_start
is no longer less than second_while_count
.
To get your "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 ;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With