CASE#

Synopsis#

Simple case:

CASE
  WHEN condition THEN statements
  [ ... ]
  [ ELSE statements ]
END CASE

Searched case:

CASE expression
  WHEN expression THEN statements
  [ ... ]
  [ ELSE statements ]
END

Description#

The CASE statement is an optional construct to allow conditional processing in SQL user-defined functions.

The WHEN clauses are evaluated sequentially, stopping after the first match, and therefore the order of the statements is significant. The statements of the ELSE clause are executed if none of the WHEN clauses match.

Unlike other languages like C or Java, SQL does not support case fall through, so processing stops at the end of the first matched case.

One or more WHEN clauses can be used.

Examples#

The following example shows a simple CASE statement usage:

FUNCTION simple_case(a bigint)
  RETURNS varchar
  BEGIN
    CASE a
      WHEN 0 THEN RETURN 'zero';
      WHEN 1 THEN RETURN 'one';
      ELSE RETURN 'more than one or negative';
    END CASE;
    RETURN NULL;
  END

Further examples of varying complexity that cover usage of the CASE statement in combination with other statements are available in the Example SQL UDFs.

See also#