CASE

A CASE statement allows you to specify multiple conditions for evaluation.

This scripting construct is only valid within a scripting block.

Syntax | Simple CASE Statement

CASE (expr)
    WHEN expr_val_1 THEN
        statement;
        [ statement; ... ]
    [ WHEN expr_val_2... ]
    [ ELSE
        statement;
        [ statement; ... ]
    ]
END [ CASE ] ;

Arguments

expr: Expression to be evaluated.

---

expr_val_[n]: The value to be matched against expr. If a match is found, the statement(s) in the clause are executed.

---

statement: One or more statements of the following types:

• A single SQL statement
• A control flow statement (e.g., loop or branching)
• A nested block
• A call to a stored procedure using CALL

Syntax | Boolean CASE Statement

CASE
    WHEN bool_expr THEN
        statement;
        [statement; ... ]
    [ WHEN ... ]
    [ ELSE
        statement;
        [statement; ... ]
    ]
END [ CASE ] ;

Arguments

bool_expr: Condition to be evaluated. If bool_expr evaluates to TRUE, the statement(s) in the clause are executed.

---

statement: Statements of the following types:

• A single SQL statement (including CALL)
• A control flow statement (e.g., loop or branching)
• A nested block

Usage Notes

• If more than one CASE branch matches the expression, only the first matching CASE branch is used.
• When comparing expressions, NULL does not match NULL. To explicitly test for NULL values, use IS [NOT] NULL.

Example 1 | Simple CASE Statement

CREATE PROCEDURE mkw_doku.proc1(vari STRING)
RETURNS STRING
AS
  BEGIN
    CASE (vari)
      WHEN 'rank 1' THEN
        RETURN 'first';
      WHEN 'rank 2' THEN
        RETURN 'second';
      ELSE
        RETURN 'not found';
    END;
  END;

Calling the procedure:

  CALL mkw_doku.proc1('rank 2');

Output:

Procedure 'mkw_doku.proc1' was successfully executed

Example 2 | Boolean CASE Statement

CREATE PROCEDURE mkw_doku.proc2(vari STRING)
RETURNS STRING
AS
  BEGIN
    CASE
      WHEN vari = 'rank 1' THEN
        RETURN 'first';
      WHEN vari = 'rank 2' THEN
        RETURN 'second';
      ELSE
        RETURN 'not found';
    END;
  END;

CALL mkw_doku.proc2('rank 74');

Procedure 'mkw_doku.proc2' was successfully executed