CASEcase_value
WHENwhen_value
THENstatement_list
[WHENwhen_value
THENstatement_list
] ... [ELSEstatement_list
] END CASE
Or:
CASE WHENsearch_condition
THENstatement_list
[WHENsearch_condition
THENstatement_list
] ... [ELSEstatement_list
] END CASE
The
CASE
statement for stored programs implements a complex conditional
construct. If a search_condition
evaluates to true, the corresponding SQL statement list is
executed. If no search condition matches, the statement list in
the ELSE
clause is executed. Each
statement_list
consists of one or
more statements.
If no when_value
or
search_condition
matches the value
tested and the
CASE
statement contains no ELSE
clause, a
Case not found for CASE statement error
results.
Each statement_list
consists of one
or more statements; an empty
statement_list
is not allowed. To
handle situations where no value is matched by any
WHEN
clause, use an ELSE
containing an empty BEGIN ... END
block, as
shown in this example:
DELIMITER | CREATE PROCEDURE p() BEGIN DECLARE v INT DEFAULT 1; CASE v WHEN 2 THEN SELECT v; WHEN 3 THEN SELECT 0; ELSE BEGIN END; END CASE; END; |
(The indentation used here in the ELSE
clause
is for purposes of clarity only, and is not otherwise
significant.)
The syntax of the
CASE
statement used inside stored programs
differs slightly from that of the SQL
CASE
expression described in
Section 11.3, “Control Flow Functions”. The
CASE
statement cannot have an ELSE NULL
clause,
and it is terminated with END CASE
instead
of END
.
User Comments
Note the handling of NULL values:
CASE
WHEN var IS NULL THEN SELECT 'Hello';
ELSE SELECT 'world.';
END CASE;
CASE var
WHEN NULL THEN SELECT 'Hello';
ELSE SELECT 'world.';
END CASE;
This will not output "Hello Hello" as might be expected, but will instead output "Hello world." because, in the second CASE statement, "var = NULL" is not true (see http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html for explanation of that).
Remember that the control-flow-functions behave differently.. For example, you have to use "END" instead of "END CASE" (this is noted above in the documentation, but I figured point out again, as I missed it!)
SELECT (CASE field1 WHEN 'Yes' THEN 1 WHEN 'No' THEN 0 ELSE 0 END CASE) FROM mytable; /* FAILS */
SELECT (CASE field1 WHEN 'Yes' THEN 1 WHEN 'No' THEN 0 ELSE 0 END) FROM mytable; /* WORKS */
Add your own comment.