Loop n times without using a stored procedure

Question

How can I write a loop that runs n times in MySql without using a stored procedure.

This is how I do it with a stored procedure:

DELIMITER $$
DROP PROCEDURE IF EXISTS test$$
CREATE PROCEDURE test()
BEGIN
   DECLARE count INT DEFAULT 0;
   WHILE count < 10 DO
      /**Sql statement**/
      SET count = count + 1;
   END WHILE;
END$$
DELIMITER ;

And then I execute my procedure this way:

call test();

If I remove the stored procedure and run the normal query, then it fails with this error:

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 'DECLARE count INT DEFAULT 0; WHILE count < 10 DO at line' 2

I have looked through the Internet for a solution with no luck.

Edit Based On comments:

The above stored procedure does exactly what I want: It loops 10 times and execute my sql statement. Now I want to accomplish the same thing without using a stored procedure. Something like:

DECLARE count INT DEFAULT 0;
   WHILE count < 10 DO
      /**Sql statement**/
      SET count = count + 1;
   END WHILE;

I've got no idea in mysql, but can't it be because count in a reserved keyword? — HoneyBadger, Sep 07 '15 at 07:51
it is unclear what you are asking for. You have your stored procedure, and you call your procedure. After that you removed the procedure and *run the normal query* like `SELECT 10;` ? if so you will never get that error message you posted. So show us the query you are trying to run. — Alex, Sep 07 '15 at 20:48
I understand, that it doesn't really answer the question, but what does your main query do? Maybe it is possible to rewrite it into a single query that does the same without explicit loop. — Vladimir Baranov, Sep 08 '15 at 00:11
@Alex this stored procedure do exactly what I want, it runs 10 times and execute my sql statement. Now i want to accomplish the same thing without using a store procedure, I will update my question to make it clear — Luthando Ntsekwa, Sep 08 '15 at 04:49
MySQL supports the IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT constructs for flow control **within stored programs**. https://dev.mysql.com/doc/refman/5.6/en/flow-control-statements.html So, it looks like you can run a loop only within a stored procedure. — Vladimir Baranov, Sep 08 '15 at 07:59
@VladimirBaranov it would be nice if you could add your comment as an answer with more details and links, this would help future visitors — Luthando Ntsekwa, Sep 08 '15 at 09:06
@LuthandoLoot Can you hint about what is your `Sql statement`? Because I have some ideas but depend on if that is an `update` or a `select` or calling a `function` with/without parameter — Juan Carlos Oropeza, Sep 10 '15 at 19:21
@JuanCarlosOropeza my sql statement is a basic INSERT query, like `INSERT INTO table_name(col1,col2,col3) VALUES("val1","val2",count) ` — Luthando Ntsekwa, Sep 11 '15 at 04:41

score 38 · Accepted Answer · edited May 23 '17 at 12:18

MySQL docs on Flow Control Statements say:

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

Docs on Stored Programs and Views say:

Stored program definitions include a body that may use compound statements, loops, conditionals, and declared variables.

Compound-Statement Syntax

This section describes the syntax for the BEGIN ... END compound statement and other statements that can be used in the body of stored programs: Stored procedures and functions, triggers, and events.

A compound statement is a block that can contain other blocks; declarations for variables, condition handlers, and cursors; and flow control constructs such as loops and conditional tests.

So, it looks like you can run an explicit loop only within a stored procedure, function or trigger.

Depending on what you do in your SQL statement, it may be acceptable to use a table (or view) of numbers (Creating a "Numbers Table" in mysql, MYSQL: Sequential Number Table).

If your query is a SELECT and it is OK to return result of your SELECT 10 times as one long result set (as opposed to 10 separate result sets) you can do something like this:

SELECT MainQuery.*
FROM
    (
        SELECT 1 AS Number 
        UNION ALL SELECT 2
        UNION ALL SELECT 3
        UNION ALL SELECT 4
        UNION ALL SELECT 5
        UNION ALL SELECT 6
        UNION ALL SELECT 7
        UNION ALL SELECT 8
        UNION ALL SELECT 9
        UNION ALL SELECT 10
    ) AS Numbers
    CROSS JOIN
    (
        SELECT 'some data' AS Result
    ) AS MainQuery

Example for INSERT

I recommend to have a permanent table of numbers in your database. It is useful in many cases. See the links above how to generate it.

So, if you have a table Numbers with int column Number with values from 1 to, say, 100K (as I do), and primary key on this column, then instead of this loop:

DECLARE count INT DEFAULT 0;
WHILE count < 10 DO
    INSERT INTO table_name(col1,col2,col3) 
    VALUES("val1","val2",count);

    SET count = count + 1;
END WHILE;

you can write:

INSERT INTO table_name(col1,col2,col3)
SELECT ("val1", "val2", Numbers.Number-1)
FROM Numbers
WHERE Numbers.Number <= 10;

It would also work almost 10 times faster.

@LuthandoLoot, thank you. You can use a table of numbers with `INSERT` as well. Instead of 10 separate statements in a loop you can write one statement that inserts 10 rows at once. — Vladimir Baranov, Sep 11 '15 at 10:24

Bernd Buffen · Answer 2 · 2015-09-10T04:56:01.787

You can do it direcly with MariaDB Sequence Engine. MariaDB is a binary replacement for MySQL.

"A Sequence engine allows the creation of ascending or descending sequences of numbers (positive integers) with a given starting value, ending value and increment."

[Manual Sequence Engine]

Here are some Samples:

    mysql -uroot -p
    Enter password: xxxxxxx
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 5
    Server version: 10.0.20-MariaDB-log Homebrew

    Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    MariaDB [(none)]> use tmp
    Database changed
    MariaDB [tmp]> select version();
    +---------------------+
    | version()           |
    +---------------------+
    | 10.0.20-MariaDB-log |
    +---------------------+
    1 row in set (0.00 sec)

    MariaDB [tmp]> select * from seq_1_to_10;
    +-----+
    | seq |
    +-----+
    |   1 |
    |   2 |
    |   3 |
    |   4 |
    |   5 |
    |   6 |
    |   7 |
    |   8 |
    |   9 |
    |  10 |
    +-----+
    10 rows in set (0.00 sec)

    MariaDB [tmp]> select * from seq_1_to_10_step_2;
    +-----+
    | seq |
    +-----+
    |   1 |
    |   3 |
    |   5 |
    |   7 |
    |   9 |
    +-----+
    5 rows in set (0.00 sec)

MariaDB [tmp]> SELECT DAYNAME('1980-12-05' + INTERVAL (seq) YEAR) day,
    ->     '1980-12-05' + INTERVAL (seq) YEAR date FROM seq_0_to_40;
+-----------+------------+
| day       | date       |
+-----------+------------+
| Friday    | 1980-12-05 |
| Saturday  | 1981-12-05 |
| Sunday    | 1982-12-05 |
| Monday    | 1983-12-05 |
| Wednesday | 1984-12-05 |
| Thursday  | 1985-12-05 |
| Friday    | 1986-12-05 |
| Saturday  | 1987-12-05 |
| Monday    | 1988-12-05 |
| Tuesday   | 1989-12-05 |
| Wednesday | 1990-12-05 |
| Thursday  | 1991-12-05 |
| Saturday  | 1992-12-05 |
| Sunday    | 1993-12-05 |
| Monday    | 1994-12-05 |
| Tuesday   | 1995-12-05 |
| Thursday  | 1996-12-05 |
| Friday    | 1997-12-05 |
| Saturday  | 1998-12-05 |
| Sunday    | 1999-12-05 |
| Tuesday   | 2000-12-05 |
| Wednesday | 2001-12-05 |
| Thursday  | 2002-12-05 |
| Friday    | 2003-12-05 |
| Sunday    | 2004-12-05 |
| Monday    | 2005-12-05 |
| Tuesday   | 2006-12-05 |
| Wednesday | 2007-12-05 |
| Friday    | 2008-12-05 |
| Saturday  | 2009-12-05 |
| Sunday    | 2010-12-05 |
| Monday    | 2011-12-05 |
| Wednesday | 2012-12-05 |
| Thursday  | 2013-12-05 |
| Friday    | 2014-12-05 |
| Saturday  | 2015-12-05 |
| Monday    | 2016-12-05 |
| Tuesday   | 2017-12-05 |
| Wednesday | 2018-12-05 |
| Thursday  | 2019-12-05 |
| Saturday  | 2020-12-05 |
+-----------+------------+
41 rows in set (0.00 sec)

MariaDB [tmp]>

Here one Sample:

MariaDB [(none)]> use tmp
Database changed
MariaDB [tmp]> SELECT * FROM seq_1_to_5,
    -> (SELECT * FROM animals) AS x
    -> ORDER BY seq;
+-----+------+-----------+-----------------+
| seq | id   | name      | specie          |
+-----+------+-----------+-----------------+
|   1 |    1 | dougie    | dog-poodle      |
|   1 |    6 | tweety    | bird-canary     |
|   1 |    5 | spotty    | turtle-spotted  |
|   1 |    4 | mr.turtle | turtle-snapping |
|   1 |    3 | cadi      | cat-persian     |
|   1 |    2 | bonzo     | dog-pitbull     |
|   2 |    4 | mr.turtle | turtle-snapping |
|   2 |    3 | cadi      | cat-persian     |
|   2 |    2 | bonzo     | dog-pitbull     |
|   2 |    1 | dougie    | dog-poodle      |
|   2 |    6 | tweety    | bird-canary     |
|   2 |    5 | spotty    | turtle-spotted  |
|   3 |    6 | tweety    | bird-canary     |
|   3 |    5 | spotty    | turtle-spotted  |
|   3 |    4 | mr.turtle | turtle-snapping |
|   3 |    3 | cadi      | cat-persian     |
|   3 |    2 | bonzo     | dog-pitbull     |
|   3 |    1 | dougie    | dog-poodle      |
|   4 |    2 | bonzo     | dog-pitbull     |
|   4 |    1 | dougie    | dog-poodle      |
|   4 |    6 | tweety    | bird-canary     |
|   4 |    5 | spotty    | turtle-spotted  |
|   4 |    4 | mr.turtle | turtle-snapping |
|   4 |    3 | cadi      | cat-persian     |
|   5 |    5 | spotty    | turtle-spotted  |
|   5 |    4 | mr.turtle | turtle-snapping |
|   5 |    3 | cadi      | cat-persian     |
|   5 |    2 | bonzo     | dog-pitbull     |
|   5 |    1 | dougie    | dog-poodle      |
|   5 |    6 | tweety    | bird-canary     |
+-----+------+-----------+-----------------+
30 rows in set (0.00 sec)

MariaDB [tmp]>

thanks Bernd, but i do not see where you are doing a While Loop/Iteration in this example — Luthando Ntsekwa, Sep 10 '15 at 04:43
The Sequence Engine do it for you. If You use a Tablename like "seq_from_to_step" it create a for loop.. You can it direct use with select * from seq_1_to_10. I will edit my answer. — Bernd Buffen, Sep 10 '15 at 04:49
Beautiful! Since I use Maria, this is a WAY better than any others. I was about to suck it up and write a stored proc for 1 thing... Generating 20 UUIDs. `SELECT UUID() FROM seq_1_to_20` So much better! I should really read up on their other engines, too. I'm probably missing out on some nice things — Oberst, Mar 30 '16 at 19:57
Hi I'm late for the party. May I ask one question? Currently I have a similar request as the OP however I only have Select permission for a MySQL db. Is it possible to connect to it with Mariadb? And is it possible to get gui interface? Thank you! — Nicholas Humphrey, Aug 17 '18 at 02:05
You missed prerequisites in your answer: 1) Migrate your DB from MySQL to Maria 2) Uninstall MySQL 3) Install Maria 4) Update your own code to use Maria libraries for connection 5) Test your software with new database 6) Fix incompatibilities 7) Deploy new version to servers. Finally you can use Sequence engine! — m1ld, Nov 25 '20 at 07:47

score 2 · Answer 3 · answered Sep 10 '15 at 22:01

2

It is no possible.

I read all MySQL documentation and it is sentences can only be declared in function/procedure body.

answered Sep 10 '15 at 22:01

Ivan Cachicatari

4,212
2
21
41

score 0 · Answer 4 · answered Jun 07 '19 at 12:54

As mentioned by Berd you could do it with the built in sequence but it's a bit strange:

SET @i = 1;
set @str = 'a,b,c,d,e,f,g,h';

select temp.length into @length from 
(select
        ROUND(   
            (
                LENGTH(dt.data)
                - LENGTH( REPLACE (dt.data, ",", "") ) 
            ) / LENGTH(",")        
        )+1 AS length   
     from (select @str as data) dt
 ) temp;

SET @query = CONCAT('select substring_index(
    substring_index(@str, '','', seq), 
    '','', 
    -1
  ) as letter from seq_', @i, '_to_',@length);

PREPARE q FROM @query;
EXECUTE q;

score -2 · Answer 5 · answered Apr 15 '21 at 07:19

-2

DECLARE @count INT = 0;

   WHILE @count < 10 
    BEGIN
     DELETE TOP (300)
     FROM EmployeeInformation
     WHERE IsSynced=1 and CreatedDate<'2020-02-29'
     SET @count = @count + 1;
   END

This code is working for me.

answered Apr 15 '21 at 07:19

Kona Suresh

1,836
1
15
25

5

FYI, in MySQL you cannot use `DECLARE` outside of a stored routines. Hence not what the OP asked for. – amaster Sep 28 '21 at 13:27

Loop n times without using a stored procedure

5 Answers5

Linked