Conditional Logic

Follow the best practices in this section when you are using IF or CASE statements in stored programs.

When you need to write conditional logic that has several mutually exclusive clauses (in other words, if one clause is TRUE, no other clause evaluates to TRUE), use the ELSEIF construct:

IF condA THEN ... ELSEIF condB THEN ... ELSEIF condN THEN ... ELSE ... END IF;

Example

At first glance, the following statement makes sense, but on closer examination, it's a mess:

CREATE PROCEDURE process_lineitem(line_in INT) BEGIN IF line_in = 1 THEN CALL process_line1( ); END IF; IF line_in = 2 THEN CALL process_line2( ); END IF; ... IF line_in = 2045 THEN CALL process_line2045( ); END IF; END$$

Every IF statement is executed and each condition is evaluated. You should rewrite such logic as follows:

CREATE PROCEDURE process_lineitem(line_in INT) BEGIN IF line_in = 1 THEN CALL process_line1( ); ELSEIF line_in = 2 THEN CALL process_line2( ); /*... */ ELSEIF line_in = 2045 THEN CALL process_line2045( ); END IF; END$$

Benefits

This structure clearly expresses the underlying "reality" of your business logic: if one condition is TRUE, no others can be TRUE.

ELSEIF offers the most efficient implementation for processing mutually exclusive clauses. When one clause evaluates to TRUE, all subsequent clauses are ignored.

The real world is very complicated; the software we write is supposed to map those complexities into applications. The result is that we often end up needing to deal with convoluted logical expressions.

You should write your IF statements in such a way as to keep them as straightforward and understandable as possible. For example, expressions are often more readable and understandable when they are stated in a positive form. Consequently, you are probably better off avoiding the NOT operator in conditional expressions.

Example

It's not at all uncommon to write or maintain code that is structured like this:

IF condA AND NOT (condB OR condC) THEN CALL proc1; ELSEIF condA AND (condB OR condC) THEN CALL proc2; ELSEIF NOT condA AND condD THEN CALL proc3; END IF;

It's also fairly common to get a headache trying to make sense of all of that. You can often reduce the trauma by trading off the simplicity of the IF statement itself (one level of IF and ELSEIF conditions) for the simplicity of clauses within multiple levels:

IF condA THEN IF (condB OR condC) THEN CALL proc2; ELSE CALL proc1; END IF; ELSEIF condD THEN CALL proc3 END IF;

Don't forget, by the way, to take into account the possibility of your expressions evaluating to NULL. This can throw a monkey wrench into your conditional processing.

Benefits

Following this best practice will make your code easier to read and maintain.

Breaking an expression into smaller pieces can aid maintainability; if and when the logic changes, you can change one IF clause without affecting the logic of others.

Challenges

Multiple levels of nested IF statements can also decrease readability. You need to strive for a workable balance.

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 FOR 1339; DECLARE CONTINUE HANDLER FOR no_matching_case SET not_found=1 CASE WHEN (sale_value>200) THEN CALL free_shipping(sale_id); CASE customer_status WHEN 'PLATINUM' THEN CALL apply_discount(sale_id,20); WHEN 'GOLD' THEN CALL apply_discount(sale_id,15); WHEN 'SILVER' THEN CALL apply_discount(sale_id,10); WHEN 'BRONZE' THEN CALL apply_discount(sale_id,5); END CASE; END CASE;

See Chapter 5 for more details.

Any conditional statement that can be expressed as an IF statement can also be expressed as a CASE statementand vice versa. While you might heatedly debate the relative benefits of each over a few beers after work, it's fairly clear that you can write high-quality code no matter which statement you employ.

However, randomly alternating between the two statements does not lead to high-quality code. It's harder to compare the logic of two routines iffor instanceone expresses its branching logic with the CASE statement while the other uses IF. So try not to mix IF and CASE arbitrarily within your programs.