What's Aurora MySQL WHILE - DO statement correct syntax?

Question

I am trying to create a WHILE loop in Aurora MySQL. With this syntax:

SET @inc = 0;

WHILE @inc <= 5 DO
    SET @inc = @inc + 1;
END;

I getting syntax error near 'WHILE @inc <= 5 DO

What's the correct syntax in Aurora MySQL?

My AURORA_VERSION() is 2.10.0

Thanks

Shiv · Accepted Answer

You need to add a delimiter as explained [here ](https://stackoverflow.com/questions/23598218/mysql-syntax-error-in-while-statement)

The following works for me when I test it on Aurora MySQL 2.10.0: 
```
delimiter //
CREATE PROCEDURE dowhile()
  BEGIN
   DECLARE v1 INT DEFAULT 5;
    WHILE v1 > 0 DO
     SET v1 = v1 - 1;
   END WHILE;
 END; //
delimiter ;
```

roi · Answer

According to [this answer](https://stackoverflow.com/a/32454598/98806) on Stack Overflow, you can only use `WHILE` if you're inside a **stored program** or function body.

----

EDIT: [Shiv](https://repost.aws/community/users/USOHeZyk_pSqanVKfKksRyvA) has correctly identified the problem in relation to a missing `delimiter` reassignment. A good explanation of why the delimiter is sometimes necessary is given in the [MySQL docs](https://dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html):

>If you use the mysql client program to define **a stored program containing semicolon** characters, a problem arises. By default, mysql itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server.

The example uses `//` as a temporary delimiter, but any character or multiple characters (except backslash) can be used, for example `$$` is commonly seen. The idea is to nest the stored program inside a pair of `delimiter` statements:

```mysql
delimiter $$          -- <- set delimiter temorarily to $$
/* stored program definition goes here, including semicolons */
delimeter;            -- <- restore delimiter to default (semicolon)
```

pprakash · Answer

`WHILE` statement is part of [Flow Control Statements](https://dev.mysql.com/doc/refman/8.0/en/flow-control-statements.html)

> MySQL supports the `IF`, `CASE`, `ITERATE`, `LEAVE LOOP`, `WHILE`, and `REPEAT` constructs for flow control within stored programs.

What's Aurora MySQL WHILE - DO statement correct syntax?

Add your answer

Relevant content