docs/control-flow/case-stmt.md
Executes thenStmtN for the first optN that equals expr or elseStmt if no optN matches expr.
This is called a simple case statement.
Executes thenStmtN for the first condN evaluating to true, or elseStmt if no condN evaluates to true.
This is called a searched case statement.
For case expressions that yield result values, see CASE expression)
This statement may only be used within a compound statement.
CASE expr
{ WHEN opt THEN { thenStmt ; } [...] } [...]
[ ELSE { elseStmt ; } [...] ]
END CASE
CASE
{ WHEN cond THEN { thenStmt ; } [...] } [...]
[ ELSE { elseStmt ; } [...] ]
END CASE
expr: Any expression for which a comparison is defined.opt: An expression with a least common type with expr and all other optN.thenStmt: A SQL Statement to execute if preceding condition is true.elseStmt: A SQL Statement to execute if no condition is true.cond: A BOOLEAN expression.Conditions are evaluated in order, and only the first set of stmt for which opt or cond evaluate to true will be executed.
-- a simple case statement
> BEGIN
DECLARE choice INT DEFAULT 3;
DECLARE result STRING;
CASE choice
WHEN 1 THEN
VALUES ('one fish');
WHEN 2 THEN
VALUES ('two fish');
WHEN 3 THEN
VALUES ('red fish');
WHEN 4 THEN
VALUES ('blue fish');
ELSE
VALUES ('no fish');
END CASE;
END;
red fish
-- A searched case statement
> BEGIN
DECLARE choice DOUBLE DEFAULT 3.9;
DECLARE result STRING;
CASE
WHEN choice < 2 THEN
VALUES ('one fish');
WHEN choice < 3 THEN
VALUES ('two fish');
WHEN choice < 4 THEN
VALUES ('red fish');
WHEN choice < 5 OR choice IS NULL THEN
VALUES ('blue fish');
ELSE
VALUES ('no fish');
END CASE;
END;
red fish