MySQL 5.7 Cannot find error in simple WHILE statement

Question

I am trying to use a WHILE loop in MySQL v5.7 and keep getting a syntax error. I haven't been able to identify the problem. The syntax looks correct according to the documentation.

I found a thread here suggesting wrapping the statement in a DELIMITER, but this did not work either. The code is:

SET @counter = 1;

WHILE (@counter < 2) DO
    SELECT @counter;
    @counter = @counter + 1;
END WHILE

and the error message is:

ERROR 1064 (42000) at line 22: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHILE (@counter < 2) DO SELECT @counter' at line 1

What am I missing?

Yes, I tried with and without a semi-colon after the END WHILE. When using the delimiter, I put DELIMITER // before the above block and // after (with and without the semi-colon). — Jonathan
– Jonathan, Commented Sep 16, 2017 at 7:37

Ruben_PH · Accepted Answer · 2017-09-16 03:09:55Z

5

As far as I remember, you cannot use WHILE LOOP just like that. You have to put it inside a Stored Procedure like so:

CREATE PROCEDURE mysp()
BEGIN
    DECLARE counter int DEFAULT 1;
    WHILE counter < 2 DO
        SET counter = counter + 1;
    END WHILE;
END

answered Sep 16, 2017 at 3:09

Ruben_PH

1,8248 gold badges27 silver badges42 bronze badges

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

2 Comments

tadman Over a year ago

Don't forget DELIMITER

Jonathan Over a year ago

This is the closest so far. The above code, with the DELIMITER, gives me a different error: ERROR 1044 (42000) at line 18: Access denied for user 'run_OT1FwXgLf31'@'localhost' to database 'run_ot1fwxglf31' Looks like I can't create a procedure in this environment :-(

Ferdinand Gaspar · Accepted Answer · 2017-09-16 20:51:50Z

4

Try the following code. It ran successfully on my MySQL 5.7

DELIMITER //

CREATE PROCEDURE mysp()
BEGIN
   DECLARE counter INT;
   SET counter = 1;

   label1: WHILE counter < 2 DO
     SELECT counter;
     SET counter = counter + 1;
   END WHILE label1;
END; //

DELIMITER ;

answered Sep 16, 2017 at 20:51

Ferdinand Gaspar

2,0831 gold badge10 silver badges17 bronze badges

1 Comment

Hoang Duc Nguyen Over a year ago

Your sample procedure is exemplary. Two thing I found missing in my simple procedure after reading your script: 1) The ; after last END, event with different DELIMITER declared. 2) The label label1 for the while loop However I can safely remove the label if I don't want to and I can still successfully create my procedure. Many thanks!!!

Collectives™ on Stack Overflow

MySQL 5.7 Cannot find error in simple WHILE statement

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related