CODEWITHSUNDEEP
mysqlstored-procedureswhile-loop
MRX
MRX

Reputation: 1651

Mysql nested while loop in a Stored Procedure

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.

Upvotes: 8

Views: 21522

Answers (1)

vee
vee

Reputation: 38645

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 ;

Upvotes: 16

Related Questions