CODEWITHSUNDEEP
mysqlstored-procedures
Ariful Islam
Ariful Islam

Reputation: 7675

WHILE Loop in MySql procedure returned multiple result

I am using the following stored procedure

DELIMITER // 
CREATE PROCEDURE SP_WHILE (IN param1 INT)
BEGIN
    DECLARE variable1 INT;
    SET variable1 = 0;

    WHILE variable1 < param1 DO
        SELECT variable1 AS id;
        SET variable1 = variable1 + 1;
    END WHILE;
END //

CALL SP_WHILE (5);

to get the following result:

+----+
| id |
+----+
|  0 |
|  1 |
|  2 |
|  3 |
|  4 |
+----+

But currently it show 5 different result in 5 different window. How can I solve this? Please help me with giving your valuable suggestion.

Upvotes: 0

Views: 1307

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

Yes thats how its more likely to happen. To avoid that you can create a temporary table and store the values in there and then do a select at the end something as

DELIMITER // 
CREATE PROCEDURE SP_WHILE (IN param1 INT)
BEGIN
    DECLARE variable1 INT;
    SET variable1 = 0;
    create temporary table test_1 (id int);
    WHILE variable1 < param1 DO
        insert into test_1 values (variable1);
        SET variable1 = variable1 + 1;
    END WHILE;
    select * from test_1 ;
END //
delimiter ;

So this how it looks like in mysql

mysql> DELIMITER // 
mysql> CREATE PROCEDURE SP_WHILE (IN param1 INT)
    -> BEGIN
    ->     DECLARE variable1 INT;
    ->     SET variable1 = 0;
    ->     create temporary table test_1 (id int);
    ->     WHILE variable1 < param1 DO
    ->         insert into test_1 values (variable1);
    ->         SET variable1 = variable1 + 1;
    ->     END WHILE;
    ->     select * from test_1 ;
    -> END //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> CALL SP_WHILE (5) ;
+------+
| id   |
+------+
|    0 |
|    1 |
|    2 |
|    3 |
|    4 |
+------+
5 rows in set (0.03 sec)

Upvotes: 4

Related Questions