CODEWITHSUNDEEP
mysqlsqldatabasesql-insert
adam
adam

Reputation: 63

SQL - Triple nested while insert

I have this code for insert rows to table. I have three while nesthed themself, but this code give me 

error #1064 - bad syntax  close

    BEGIN
            WHILE p <= 5
            BEGIN
                WHILE ra <= 40
            ' on line 7.

What is wrong with this code?

 DELIMITER $$

CREATE PROCEDURE proc()
BEGIN
    DECLARE r int DEFAULT 1;
    DECLARE p int DEFAULT 1;
    DECLARE ra int DEFAULT 1;
    WHILE r <= 8 DO

        WHILE p <= 5 DO

            WHILE ra <= 40 DO

                INSERT INTO tabulka (REGAL,POLICE,RADA) VALUES(r,p,ra);
                SET ra = ra + 1;
            END WHILE;
            SET p = p + 1;
        END WHILE;
        SET r = r + 1;
    END WHILE;
END$$
DELIMITER ;

CALL proc();

enter image description here

EDIT: Now it generates only one loop: enter image description here

Upvotes: 0

Views: 86

Answers (2)

adam
adam

Reputation: 63

Okay, my mistake. I forgot to reset variables to 1 after inside loops was done. Thanks for help.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

MySQL uses WHILE DO/END WHILE for it syntax. So the stored procedure should look like this:

CREATE PROCEDURE proc()
BEGIN
    DECLARE r int DEFAULT 1;
    DECLARE p int DEFAULT 1;
    DECLARE ra int DEFAULT 1;
    WHILE r <= 8 DO
        WHILE p <= 5 DO
            WHILE ra <= 40 DO
                INSERT INTO tabulka (REGAL,POLICE,RADA) VALUES(r,p,ra);
                SET ra = ra + 1;
            END WHILE;
            SET p = p + 1;
        END WHILE;
        SET r = r + 1;
    END WHILE;
END;

Here is a little rextester.

Upvotes: 3

Related Questions