How to do While Loops?

How can we use while loops in MySQL? My test script:

BEGIN
    SELECT 0 INTO @n;
    WHILE @n < 10 DO
    SELECT @n;
    SET @n := @n +1;
    END WHILE;
END;

But it has syntax errors. I'm running the loop using the SQLyog client in a standard query window. The syntax errors are of the following form:


Error Code: 1064

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...

I've also tried to use the while loop example provided by https://dev.mysql.com/doc/refman/5.6/en/while.html but it still didn't work.

Which part of the script is wrong? (Using MySQL 5.6.)

I'm trying to make a 6 month moving average of some data so I was hoping that a while loop would be able to append the "new" 6 month average onto the "old" 6 month average through each iteration of the loop.

You can't do a for loop in an SQL editor without a stored procedure. I use MySQL Workbench to make this.

A quick stored procedure should do the job:

DROP PROCEDURE IF EXISTS proc_loop_test;
CREATE PROCEDURE proc_loop_test()
BEGIN
  DECLARE int_val INT DEFAULT 0;
  test_loop : LOOP
    IF (int_val = 10) THEN
      LEAVE test_loop;
    END IF;

    SET int_val = int_val +1;
    SELECT int_val; 
  END LOOP; 
END;

StackExchange Database Administrators Q#62812, answer score: 14