# Control Flow Statements

This topic describes the syntax to define logical control flow in SingleStore Helios Procedural SQL (PSQL) for stored procedures and user-defined scalar-valued functions (UDFs). The following control flow statements are described in this topic:

* [Statement Blocks](https://docs.singlestore.com/#N1623855750575.md)
* Conditional Control

  * [IF … THEN … END IF](https://docs.singlestore.com/#UUID-12830154-8a2c-b2c8-5cbf-a168ba7a1357.md)
  * [IF … THEN … ELSE … END IF](https://docs.singlestore.com/#UUID-fcc92734-7c7a-4292-373e-1c1afaef0887.md)
  * [IF … THEN … ELSIF … THEN … END IF](https://docs.singlestore.com/#UUID-b85ca1e0-a64a-dbf3-a51a-824f4e29e8b9.md)
* Iterative Control

  * [LOOP … END LOOP](https://docs.singlestore.com/#UUID-011c95f6-aa97-bafb-39c6-84c3fe75f26c.md)
  * [EXIT and EXIT WHEN](https://docs.singlestore.com/#UUID-50b81dca-e693-a1d8-78cf-3b873dad3524.md)
  * [WHILE … LOOP … END LOOP](https://docs.singlestore.com/#UUID-f077d877-5860-903e-f49e-c1831b86fc9d.md)
  * [FOR … LOOP … END LOOP](https://docs.singlestore.com/#UUID-0543e765-be58-56f9-37d0-da669ac98794.md)
  * [Loop Labels](https://docs.singlestore.com/#UUID-9e5326d7-18c3-1b37-0425-4aebf2ccf659.md)
  * [CONTINUE and CONTINUE WHEN](https://docs.singlestore.com/#UUID-d5cc36b3-72e5-12c1-a0a0-3e0e2a47e466.md)

## Statement Blocks

Statement blocks optionally declare variables, then enclose one or more statements, and optionally handle exceptions. A statement block acts as a single compound statement.

When executed, the statements in the `statement_list` portion of the block are processed sequentially. Each statement in a `statement_list` must be terminated with a semicolon (`;`).

Statement blocks can be nested. A statement block can appear where a statement appears. Variables cannot be defined in a nested block which override the definition of variables or parameters with the same name defined in an enclosing block.

## Syntax

```sql

[ DECLARE variable_list ] [ ...n ]
BEGIN
  statement_list
[ EXCEPTION
  when_clause_list ]
END

```

## Arguments

**variable\_list**

A variable definition or series of definitions. A sequence of zero or more `DECLARE` keywords may appear, each with its own `variable_list`.

**statement\_list**

Any valid procedural SQL statement or series of statements.

**when\_clause\_list**

The `when_clause_list` defines different possible exception conditions and statements to handle them. See the [Exceptions and Exception Handling](https://docs.singlestore.com/cloud/reference/sql-reference/procedural-sql-reference/exceptions-and-exception-handling.md) topic for more details.

## Examples

The following example adds one to the input integer and returns the result.

```sql
DELIMITER //
CREATE FUNCTION plus_one(a INT) RETURNS INT AS
  DECLARE
    b INT = a;
  BEGIN
    b += 1;
    RETURN b;
  END //
DELIMITER ;

```

```sql
SELECT plus_one(1);


```

```output

+-------------+
| plus_one(1) |
+-------------+
|           2 |
+-------------+

```

The example below illustrates nested statement blocks, and the general syntax of the `DECLARE` statements at the beginning of a block.

```sql
DELIMITER //
CREATE OR REPLACE FUNCTION nesting(i INT) RETURNS BIGINT AS
DECLARE x INT = 0;
DECLARE z1 INT; z2 VARCHAR(255);
BEGIN
  DECLARE q INT = 0;
  BEGIN
    IF q = i THEN
      DECLARE xx INT = 7;
      BEGIN
         RETURN q + xx;
      END;
    ELSE
      DECLARE q2 INT = 88;
      BEGIN
        RETURN q2;
      END;
    END IF;
  END;
END //
DELIMITER ;

```

```sql
SELECT nesting(0);


```

```output

+------------+
| nesting(0) |
+------------+
|          7 |
+------------+

```

```sql
SELECT nesting(1);


```

```output

+------------+
| nesting(1) |
+------------+
|         88 |
+------------+

```

## IF … THEN … END IF

Specifies one or more statements to be executed if a given boolean expression evaluates to `TRUE`. All `IF` blocks must be terminated with the `END IF` keyword pair.

## Syntax

```
IF boolean_expression THEN
  statement_list
END IF;

```

## Arguments

**boolean\_expression**

An expression that evaluates to `TRUE` or `FALSE`. If the expression evaluates to `TRUE`, the *statement\_list* enclosed by `THEN ... END IF` will be executed. If the expression evaluates to `FALSE`, the *statement\_list* will not be executed.

**statement\_list**

Any valid procedural SQL statement or series of statements.

## Example

The following example returns a boolean value (`1` for `TRUE` and `0` for `FALSE`) if the input integer is odd.

```sql
DELIMITER //
CREATE FUNCTION is_odd(a INT) RETURNS BOOL AS
  BEGIN
    IF a % 2 = 0 THEN
      RETURN FALSE;
    END IF;
    RETURN TRUE;
  END //
DELIMITER ;

```

```sql
SELECT is_odd(5);


```

```output

+-----------+
| is_odd(5) |
+-----------+
|         1 |
+-----------+

```

## IF … THEN … ELSE … END IF

Specifies a condition and two alternative lists of statements to execute, depending on whether the condition is true. All `IF` blocks must be terminated with the `END IF` keyword pair.

## Syntax

```
IF boolean_expression THEN
  statement_list
ELSE
  statement_list
END IF;

```

## Arguments

**boolean\_expression**

An expression that evaluates to `TRUE` or `FALSE`. If the expression evaluates to `TRUE`, the *statement\_list* enclosed by `THEN ... ELSE` will be executed. If the expression evaluates to `FALSE`, the *statement\_list* following `ELSE` will be executed instead.

**statement\_list**

Any valid procedural SQL statement or series of statements.

## Example

The following example returns a boolean value (`1` for `TRUE` and `0` for `FALSE`) if the input integer is odd.

```sql
DELIMITER //
CREATE FUNCTION is_odd(a INT) RETURNS BOOL AS
  DECLARE
    result BOOL;
  BEGIN
    IF a % 2 = 0 THEN
      result = FALSE;
    ELSE
      result = TRUE;
    END IF;
    RETURN result;
  END //
DELIMITER ;

```

```sql
SELECT is_odd(3);


```

```output

+-----------+
| is_odd(3) |
+-----------+
|         1 |
+-----------+

```

## IF … THEN … ELSIF … THEN … END IF

Specifies one or more statements to be executed if an initial boolean expression evaluates to `TRUE`. If this boolean expression evaluates to `FALSE`, one or more `ELSIF` boolean expressions are evaluated. If an `ELSIF` expression evaluates to `TRUE`, its statement list will be executed, and no additional `ELSIF` expressions will be evaluated.

An optional `ELSE` clause can also be specified after a block of `ELSIF` statements. If none of the boolean expressions preceding the `ELSE` clause evaluates to `TRUE`, then the list of statements in the `ELSE` clause are executed.

All `IF` blocks must be terminated with the `END IF` keyword pair.

## Syntax

```
IF boolean_expression THEN
  statement_list
ELSIF boolean_expression THEN
  statement_list
[...n]
[ELSE
  statement_list
]
END IF;

```

## Arguments

**boolean\_expression**

An expression that evaluates to `TRUE` or `FALSE`.

**statement\_list**

Any valid procedural SQL statement or series of statements.

## Example

The following example accepts a test score as an integer and outputs a grade letter.

```sql
DELIMITER //
CREATE FUNCTION test_grade(test_score INT) RETURNS CHAR(1) AS
  DECLARE
    grade_letter CHAR(1);
  BEGIN
    IF test_score >= 90 THEN
      grade_letter = 'A';
    ELSIF test_score >= 80 THEN
      grade_letter = 'B';
    ELSIF test_score >= 70 THEN
      grade_letter = 'C';
    ELSIF test_score >= 60 THEN
      grade_letter = 'D';
    ELSE
      grade_letter = 'F';
    END IF;
    RETURN grade_letter;
  END //
DELIMITER ;

```

```sql
SELECT test_grade(89);


```

```output

+----------------+
| test_grade(89) |
+----------------+
| B              |
+----------------+

```

## LOOP … END LOOP

Specifies one or more statements to execute repeatedly in an infinite loop. Each iteration of the loop executes the statement list and returns control to the top of the loop. Loop statements can be nested within themselves and other control statements, such as `IF ... END IF`.

## Syntax

```
LOOP
  statement_list
END LOOP;

```

## Arguments

**statement\_list**

Any valid procedural SQL statement or series of statements.

## Example

The following example creates an endless loop that adds one to the input integer. The `RETURN` statement will never get reached.

```sql
DELIMITER //
CREATE FUNCTION endless_loop(a INT) RETURNS INT AS
  DECLARE
    b INT = a;
  BEGIN
    LOOP
      b += 1;
    END LOOP;
    RETURN b;
  END //
DELIMITER ;

```

## EXIT and EXIT WHEN

Terminates the current iteration of a loop and then exits the loop body, and optionally when a boolean condition is met. `EXIT` and `EXIT WHEN` can be used in any loop body, including `LOOP`, `FOR ... LOOP`, or `WHILE ... LOOP`.

## Syntax

```
{ [FOR | WHILE ] expression } LOOP
  [statement_list]
  { EXIT | EXIT WHEN boolean_condition } [loop_label] ;
END LOOP;

```

## Arguments

**expression**

Any expression associated with the loop declaration, such as a conditional boolean expression.

**statement\_list**

Any valid procedural SQL statement or series of statements.

**boolean\_expression**

An expression that evaluates to `TRUE` or `FALSE`. If the expression evaluates to `TRUE`, the *statement\_list* preceding `EXIT` will be executed. If the expression evaluates to `FALSE`, loop will continue iterating until the boolean expression evaluates to `TRUE`.

**loop\_label**

The optional identifier for a parent loop. If specifying `EXIT` with a loop label, the label must be set on one of the parent loops.

## Examples

**IF … THEN … EXIT Example**

The following example generates text of the minimum specified length, and uses an `EXIT` statement in the body of a loop to return the result when the boolean condition is met.

```sql
DELIMITER //
CREATE FUNCTION text_generator(min_length INT) RETURNS TEXT AS
  DECLARE
    lorem_text TEXT = "Lorem ipsum dolor sit amet. ";
    result TEXT = lorem_text;
  BEGIN
    LOOP
      IF LENGTH(result) < min_length THEN
        result = CONCAT(result, lorem_text);
      ELSE
        EXIT;
      END IF;
    END LOOP;
    RETURN result;
  END //
DELIMITER ;

```

```sql
SELECT text_generator(50);


```

```output

+----------------------------------------------------------+
| text_generator(50)                                       |
+----------------------------------------------------------+
| Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet.  |
+----------------------------------------------------------+

```

**EXIT WHEN Example**

The following example is similar to the `LOOP ... IF ... EXIT` example and generates text of the minimum specified length. When the length is reached as specified in the `WHEN` expression, the loop is exited.

```sql
DELIMITER //
CREATE FUNCTION text_generator(min_length INT) RETURNS TEXT AS
  DECLARE
    lorem_text TEXT = "Lorem ipsum dolor sit amet. ";
    result TEXT = lorem_text;
  BEGIN
    LOOP
      EXIT WHEN LENGTH(result) >= min_length;
      result = CONCAT(result, lorem_text);
    END LOOP;
    RETURN result;
  END //
DELIMITER ;

```

```sql
SELECT text_generator(50);


```

```output

+----------------------------------------------------------+
| text_generator(50)                                       |
+----------------------------------------------------------+
| Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet.  |
+----------------------------------------------------------+

```

## WHILE … LOOP … END LOOP

Specifies an initial boolean condition and a list of statements. If the expression evaluates to `TRUE`, the loop will begin execution of the statement list. After each statement is executed, control will be returned to the top of the loop and the boolean expression will be evaluated again. If the expression evaluates to `FALSE`, the loop will immediately exit.

## Syntax

```
WHILE boolean_expression LOOP
  statement_list
END LOOP;

```

## Arguments

**boolean\_expression**

An expression that evaluates to `TRUE` or `FALSE`.

**statement\_list**

Any valid procedural SQL statement or series of statements.

## Example

The following example is similar to the `LOOP ... IF ... EXIT` and `LOOP ... EXIT WHEN` examples, and generates text of the minimum specified length. While the length remains shorter than the desired length, the loop will continue execution. As soon as the condition is met, the loop immediately exits.

```sql
DELIMITER //
CREATE FUNCTION text_generator(min_length INT) RETURNS TEXT AS
  DECLARE
    lorem_text TEXT = "Lorem ipsum dolor sit amet. ";
    result TEXT = lorem_text;
  BEGIN
    WHILE LENGTH(result) < min_length LOOP
      result = CONCAT(result, lorem_text);
    END LOOP;
    RETURN result;
  END //
DELIMITER ;

```

```sql
SELECT text_generator(50);


```

```output

+----------------------------------------------------------+
| text_generator(50)                                       |
+----------------------------------------------------------+
| Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet.  |
+----------------------------------------------------------+

```

## FOR … LOOP … END LOOP

Specifies a loop with a fixed number of iterations using either the size of an array or a range of integer values as the iteration scheme. `FOR` loops support reverse iteration using the `REVERSE` keyword and explicit loop strides by specifying an integer value with the `BY` keyword.

## Syntax

**Array Range FOR Loop Syntax**

```
FOR counter_variable IN array_expression LOOP
  statement_list
END LOOP;

```

**Integer Range FOR Loop Syntax**

```
FOR counter_variable IN [REVERSE]
      lower_bound .. upper_bound [BY stride_count] LOOP
  statement_list
END LOOP;

```

## Arguments

**counter\_variable**

A variable that is scoped to the body of the `FOR` loop and must be unique within this scope.

**array\_expression**

An array expression that references an initialized array with a defined length.

**lower\_bound**

An expression that evaluates to an integer value, which represents the lower bound (starting value) of the iteration scheme.

**upper\_bound**

An expression that evaluates to an integer value, which represents the upper bound (ending value) of the iteration scheme.

**stride\_count**

An expression that evaluates to an integer value, which increments the current iteration count by the specified amount after the statement list in the loop body has been executed.

**statement\_list**

Any valid procedural SQL statement or series of statements.

## Remarks

When using a `FOR` loop on a variable that references an array, the loop creates a read-only copy of the array and uses it as the iteration scheme. Therefore, if both the loop declaration and the loop body reference the same array, the loop’s iteration scheme is not affected by any modifications to the array. Instead, the modifications are applied outside the scope of the loop. Consider the following example:

```sql
DELIMITER //
CREATE FUNCTION array_modification() RETURNS VARCHAR(255) AS
  DECLARE
    myarray ARRAY(INT) = [1, 2, 3];
    loop_count INT = 0;
  BEGIN
    FOR i IN myarray LOOP
      myarray = [1, 2, 3, 4];
      loop_count += 1;
    END LOOP;
    RETURN CONCAT("loop_count = ", loop_count,
                    ", myarray size = ", LENGTH(myarray));
  END //
DELIMITER ;

```

This example declares an `myarray` with a size of 3, then loops over the array. In the loop body, the `myarray` value is changed. However, the loop executes only 3 times. After the loop exits, `myarray` is updated to the new value with a length of 4. The results are shown below:

```sql
SELECT array_modification();


```

```output

+----------------------------------+
| array_modification()             |
+----------------------------------+
| loop_count = 3, myarray size = 4 |
+----------------------------------+

```

## Examples

**FOR Loop on Array Range Example**

The following example declares an array and then loops over the array to count its size:

```sql
DELIMITER //
CREATE FUNCTION count_array() RETURNS INT AS
  DECLARE
    myarray ARRAY(INT) = [1, 2, 3];
    count INT = 0;
  BEGIN
    FOR i IN myarray LOOP
      count += 1;
    END LOOP;
    RETURN count;
  END //
DELIMITER ;

```

```sql
SELECT count_array();


```

```output

+---------------+
| count_array() |
+---------------+
|             3 |
+---------------+

```

**FOR Loop on Integer Range Example**

The following example uses a `FOR` loop to computes the factorial of the input integer and returns it.

```sql
DELIMITER //
CREATE FUNCTION factorial(num INT) RETURNS INT AS
  DECLARE
    factor INT = 1;
  BEGIN
    FOR i IN 1 .. num LOOP
      factor = factor * i;
    END LOOP;
    RETURN factor;
  END //
DELIMITER ;

```

```sql
SELECT factorial(5);


```

```output

+--------------+
| factorial(5) |
+--------------+
|          120 |
+--------------+

```

**Reverse FOR Loop Example**

The following example uses a reversed `FOR` loop to count down from the specified input number and returns a text string. Note that the larger number must precede the smaller number in the loop declaration when `REVERSE` is specified.

```sql
DELIMITER //
CREATE FUNCTION countdown(num INT) RETURNS VARCHAR(255) AS
  DECLARE
    output VARCHAR(255) = "Countdown: ";
  BEGIN
    FOR i IN REVERSE num .. 1 LOOP
      output = CONCAT(output, i, ", ");
    END LOOP;
    output = CONCAT(output, "Launch!");
    RETURN output;
  END //
DELIMITER ;

```

```sql
SELECT countdown(10);


```

```output

+---------------------------------------------------+
| countdown(10)                                     |
+---------------------------------------------------+
| Countdown: 10, 9, 8, 7, 6, 5, 4, 3, 2, 1, Launch! |
+---------------------------------------------------+

```

**FOR Loop with Stride Length**

The following example uses a reverse loop with a stride length of 2 to count down from the input integer and output each even number.

```sql
DELIMITER //
CREATE OR REPLACE FUNCTION countdown_evens(num INT) RETURNS VARCHAR(255) AS
  DECLARE
    output VARCHAR(255) = "Countdown Evens: ";
    start INT = num;
  BEGIN
    IF num % 2 = 1 THEN
      start = start - 1;
    END IF;
    output = CONCAT(output, start);
    start = start - 2;
    FOR i IN REVERSE start .. 2 BY 2 LOOP
      output = CONCAT(output, ", ", i);
    END LOOP;
    RETURN output;
  END //
DELIMITER ;

```

```sql
SELECT countdown_evens(11);


```

```output

+---------------------------------+
| countdown_evens(11)             |
+---------------------------------+
| Countdown Evens: 10, 8, 6, 4, 2 |
+---------------------------------+

```

## Loop Labels

Loops can be labeled using unique identifier syntax, which makes it easier to identify a specific loop in a series of nested loops. A loop label must be declared before the `LOOP` keyword, but optionally be labeled after the terminating `END LOOP` keyword pair.

Loop labels can be used to terminate any loop within the scope of a nested loop by using an `EXIT` statement. For example, a nested loop can terminate one or more parent loops in addition to itself, effectively short-circuiting any desired parent loops. Loop labels can also be used with the `CONTINUE` statement for conditional loops to return control to the initial conditional expression. See the examples below for more information.

## Syntax

```
<<label_name>>
{ WHILE boolean_expression LOOP | LOOP }
  statement_list
END LOOP [label_name];

```

## Arguments

**label\_name**

An identifier for the loop, enclosed by double angle brackets (`<<` and `>>`) for the initial declaration. Loop labels must be unique within the scope of the function body, and cannot conflict with any other identifiers such as input parameters, variables, and so on.

**boolean\_expression**

An expression that evaluates to `TRUE` or `FALSE`.

**statement\_list**

Any valid procedural SQL statement or series of statements.

## Examples

**Loops Labels with EXIT**

The following example demonstrates loop labels for `EXIT` statements by creating a nested loop where the outer loop is named `parent` and the inner loop is named `child`.

```sql
DELIMITER //
CREATE FUNCTION parent_child_loop(a INT) RETURNS INT AS
  DECLARE
    b INT = a;
  BEGIN
    <<parent>>
    LOOP
      <<child>>
      LOOP
        EXIT parent WHEN a >= 100;
        b += 1;
      END LOOP child;
      RETURN 200;
    END LOOP parent;
    RETURN b;
  END //
DELIMITER ;

```

When this function is executed, the outer loop’s `RETURN` statement is never reached. Instead, the inner loop’s `EXIT parent` statement is executed when the input value meets or exceeds 100:

```sql
SELECT parent_child_loop(1);


```

```output

+----------------------+
| parent_child_loop(1) |
+----------------------+
|                  100 |
+----------------------+

```

**Loop Labels with CONTINUE**

The following example demonstrates loop labels for `CONTINUE` statements by creating a single `WHILE` loop.

```sql
DELIMITER //
CREATE FUNCTION continue_loop_label(a INT) RETURNS INT AS
  DECLARE
    b INT = a;
  BEGIN
    <<myloop>>
    WHILE b < 10 LOOP
      b += 1;
      CONTINUE myloop;
      EXIT myloop;
    END LOOP myloop;
    RETURN b;
  END //
DELIMITER ;

```

When this function is executed, the `EXIT myloop` statement is never reached because the preceding `CONTINUE myloop` statement forces return of control to the loop’s initial conditional statement: `WHILE a < 10 LOOP`.

```sql
SELECT continue_loop_label(1);


```

```output

+------------------------+
| continue_loop_label(1) |
+------------------------+
|                     10 |
+------------------------+

```

## CONTINUE and CONTINUE WHEN

Forces return of control to the top of a loop’s conditional expression or specifies its own conditional expression where, if satisfied, returns control to the top of the loop. `CONTINUE` syntax can be used in the following ways:

* `CONTINUE loop_label` in a labeled `WHILE boolean_expression LOOP`, where the `CONTINUE` statement returns control to the top of the `WHILE` loop.
* `CONTINUE WHEN boolean_expression` in a `LOOP`, where any statements following the `CONTINUE` statement will not be executed. Instead, control will be returned to the top of the loop.

See the examples below for more information.

## Syntax

```
[<<label_name>>]
{ WHILE boolean_expression LOOP | LOOP }
  statement_list
  { CONTINUE WHEN boolean_expression | CONTINUE label_name }
END LOOP [label_name];

```

## Arguments

**label\_name**

An identifier for the loop, enclosed by double angle brackets (`<<` and `>>`) for the initial declaration. Loop labels must be unique within the scope of the function body, and cannot conflict with any other identifiers such as input parameters, variables, and so on.

**boolean\_expression**

An expression that evaluates to `TRUE` or `FALSE`.

**statement\_list**

Any valid procedural SQL statement or series of statements.

## Examples

**CONTINUE for Labeled Loop**

The following example demonstrates a `CONTINUE` statement used inside of a labeled `WHILE` loop.

```sql
DELIMITER //
CREATE FUNCTION continue_labeled_example(a INT) RETURNS INT AS
  DECLARE
    b INT = a;
  BEGIN
    <<myloop>>
    WHILE b < 10 LOOP
      b += 1;
      CONTINUE myloop;
      EXIT myloop;
    END LOOP myloop;
    RETURN b;
  END //
DELIMITER ;

```

When this function is executed, the `EXIT myloop` statement is never reached because the preceding `CONTINUE myloop` statement forces return of control to the loop’s initial conditional statement: `WHILE a < 10 LOOP`.

```sql
SELECT continue_labeled_example(1);


```

```output

+-----------------------------+
| continue_labeled_example(1) |
+-----------------------------+
|                          10 |
+-----------------------------+

```

**CONTINUE WHEN for a Non-Labeled Loop**

The following example demonstrates loop labels for `CONTINUE` statements by creating a single loop.

```sql
DELIMITER //
CREATE OR REPLACE FUNCTION continue_when_example() RETURNS INT AS
  DECLARE
    a INT = 0;
    b INT = 0;
  BEGIN
    LOOP
      a += 1;
      CONTINUE WHEN a < 10;
      b += 1;
      EXIT WHEN b > 20;
    END LOOP;
    RETURN a;
  END //
DELIMITER ;

```

In this example, the `CONTINUE WHEN` statement forces return of control to the top of the loop until the value of `a` is greater than or equal to 10. After `a`’s value reaches 10, the `b += 1` statement will be executed until the exit condition is met.

```sql
SELECT continue_when_example();


```

```output

+-------------------------+
| continue_when_example() |
+-------------------------+
|                      30 |
+-------------------------+

```

***

Modified at: June 12, 2023

Source: [/cloud/reference/sql-reference/procedural-sql-reference/control-flow-statements/](https://docs.singlestore.com/cloud/reference/sql-reference/procedural-sql-reference/control-flow-statements/)

(An index of the documentation is available at /llms.txt)