For loop in MySQL Workbench

Question

Total noob in Mysql alert.

need to run simple For loop to insert data into table. Copying For loop example from docs:

CREATE PROCEDURE doiterate(p1 INT)
BEGIN
  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN
      ITERATE label1;
    END IF;
    LEAVE label1;
  END LOOP label1;
  SET @x = p1;
END;

Shows bunch of errors in Workbench editor window:

Eg, first error for "p1" tells:

"p1" is not valid for this position, expecting an identifier

Checked MySQL version - SELECT @@version;, its 5.7, the same as from docs page. Workbench version is 8.0

Not sure what other info I can provide.

What happens if you try to create and then run the stored proc? — Shadow
– Shadow, Commented Nov 11, 2019 at 10:29
Have you set delimiters (procedure syntaxes for me if I do) and runs fine.. — P.Salmon
– P.Salmon, Commented Nov 11, 2019 at 10:44
Thanks, wasnt able to answer quicker. Answer solved the problem — bigInt
– bigInt, Commented Nov 11, 2019 at 15:07

James · Accepted Answer · 2019-11-11 13:41:30Z

1

On MySQL Workbench

when creating a stored routine on Query Tab you need to explicitly set delimiter before create procedure..

DELIMITER $$ -- this symbol can be anything other than default ;
CREATE PROCEDURE doiterate(p1 INT)
BEGIN
  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN
      ITERATE label1;
    END IF;
    LEAVE label1;
  END LOOP label1;
  SET @x = p1;
END $$
DELIMITER ; -- once completed we are resting back to default

When creating a routine under Routine Tab you need not to include delimiter

Since when clicking Apply it will automatically builds it.

answered Nov 11, 2019 at 13:41

James

1,8292 gold badges11 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

For loop in MySQL Workbench

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related