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?

Answer 1

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

Answer 2

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 ;