SQL - Triple nested while insert

Question

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();

EDIT: Now it generates only one loop:

Gordon Linoff · Accepted Answer · 2019-03-23 11:33:09Z

3

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.

answered Mar 23, 2019 at 11:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

adam Over a year ago

Thanks, but now it only generates 40 rows with regal = 1, police = 1 and rada = 1 .... 40, but i want to generate regal 1-8 and rada 1-5

Gordon Linoff Over a year ago

@adam . . . This is your code with the syntax errors fixed -- which is the question you asked. If you have another question about logic, ask it as a new question. That said, I suspect your problem is that you do not re-initialize the variable before restarting each loop.

adam · Accepted Answer · 2019-03-23 12:07:48Z

0

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

answered Mar 23, 2019 at 12:07

adam

631 gold badge1 silver badge7 bronze badges

Collectives™ on Stack Overflow

SQL - Triple nested while insert

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related