SQL ITERATE

What is the SQL ITERATE statement?

ITERATE skips the remaining statements in the current loop iteration and immediately starts the next iteration of a labeled LOOP, WHILE, or REPEAT block.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL ITERATE: Supported: MySQL 5.0+, MariaDB 5.0+, IBM DB2 LUW, NuoDB. Not supported natively in PostgreSQL, SQL Server, Oracle, or SQLite.

SQL ITERATE Full Explanation

ITERATE is a flow-control statement available in MySQL, MariaDB, IBM DB2, and a few other SQL/PSM-compatible systems. It can only appear inside stored programs (procedures, functions, triggers, or events) and must reference the label of the loop it controls. When the statement executes, the interpreter:1. Checks that the referenced label belongs to an active LOOP, WHILE, or REPEAT block.2. Abandons all statements that remain in the current iteration after the ITERATE call.3. Transfers control to the loop’s test condition. If the condition still evaluates to TRUE, the next iteration begins; otherwise the loop ends.ITERATE behaves like the continue keyword in many general-purpose languages. It does not exit the loop (use LEAVE for that) and it cannot target outer blocks that are not loops. Only one label is allowed, and it must be written exactly as declared (case-sensitive on case-sensitive servers).

SQL ITERATE Syntax

ITERATE <loop_label>;

SQL ITERATE Parameters

- loop_label (identifier) - Name of the LOOP, WHILE, or REPEAT block whose next iteration should start.

Example Queries Using SQL ITERATE

DELIMITER $$
CREATE PROCEDURE sum_even_numbers()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE total INT DEFAULT 0;

  l1: WHILE i < 10 DO
    SET i = i + 1;

    -- Skip odd numbers
    IF i % 2 = 1 THEN
      ITERATE l1;
    END IF;

    SET total = total + i;  -- Executed only for even i
  END WHILE l1;

  SELECT total AS even_total;  -- Returns 30 (2+4+6+8+10)
END$$
DELIMITER ;

CALL sum_even_numbers();

Expected Output Using SQL ITERATE

The procedure returns a single row with even_total = 30 because ITERATE bypasses odd numbers and adds only even integers to the running total

Use Cases with SQL ITERATE

Skip unwanted rows during cursor loops without duplicating complex exit logic.
Ignore invalid data quickly and continue processing the next record.
Implement performance-critical loops where an early continue is clearer than nested IF blocks.

Common Mistakes with SQL ITERATE

Forgetting to declare or reference a label, leading to ERROR 1319 (42000): Undefined ITERATE target.
Using ITERATE outside a loop block; it must be inside LOOP, WHILE, or REPEAT.
Confusing ITERATE with LEAVE; LEAVE exits the loop entirely, ITERATE restarts it.

First Introduced In

MySQL 5.0 (2005) and IBM DB2 UDB 7.1

Frequently Asked Questions

What does ITERATE do?

ITERATE restarts the current labeled loop by skipping all remaining statements in its body and reevaluating the loop condition.

When should I use ITERATE instead of LEAVE?

Use ITERATE when you want to continue looping but ignore the rest of the current iteration. Use LEAVE when you want to exit the loop completely.

Can ITERATE be nested?

Yes. Place unique labels on each loop. ITERATE can only target an active loop label, so you can jump from an inner block to an outer loop if you name that outer loop.

Does ITERATE affect cursors?

Indirectly. If your loop fetches from a cursor, ITERATE skips any follow-up processing for that fetch but keeps the cursor open for the next iteration.