Name
IF-03: Make sure that a CASE statement is inclusive, or construct a handler to catch any unmatched cases
Synopsis
If none of the CASE
statements match as the input condition, CASE
will raise MySQL error 1339
(Case not found for CASE
statement
). You should either construct an error handler
to ignore this error, or ensure that the exception never occurs by
including an ELSE
clause in
every CASE
statement (the
easier solution).
Example
In the following example, the CASE
statement will fail if the customer
status is not one of 'PLATINUM'
, 'GOLD'
, 'SILVER'
, or 'BRONZE'
:
CASE customer_status WHEN 'PLATINUM' THEN CALL apply_discount(sale_id,20); /* 20% discount */ WHEN 'GOLD' THEN CALL apply_discount(sale_id,15); /* 15% discount */ WHEN 'SILVER' THEN CALL apply_discount(sale_id,10); /* 10% discount */ WHEN 'BRONZE' THEN CALL apply_discount(sale_id,5); /* 5% discount*/ END CASE;
Here we add an ELSE
clause to avoid the error. Since we don’t have anything for the
ELSE
clause to do, we use a
dummy SET
statement.
CASE customer_status WHEN 'PLATINUM' THEN CALL apply_discount(sale_id,20); /* 20% discount */ WHEN 'GOLD' THEN CALL apply_discount(sale_id,15); /* 15% discount */ WHEN 'SILVER' THEN CALL apply_discount(sale_id,10); /* 10% discount */ WHEN 'BRONZE' THEN CALL apply_discount(sale_id,5); /* 5% discount */ ELSE SET dummy=dummy; END CASE;
In this alternative solution, we construct a handler to allow the error to be ignored:
DECLARE not_found INT DEFAULT 0; DECLARE no_matching_case CONDITION ...
Get MySQL Stored Procedure Programming now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.