MySQL Stored Procedure Programming

Name

PRG-05: Avoid deep nesting of conditionals and loops

Synopsis

Many studies have confirmed that excessive nesting of IF, CASE, or LOOP structures leads to code that is difficult to understand. More than two or three levels of nesting is probably undesirable.

Consider the following logic:

    IF  v_state='CA' THEN
        IF  v_quantity > 100 THEN
          IF  v_customer_status='A' THEN
            IF  v_product_code='X' THEN
                SET  v_discount=.04;
            ELSEIF  v_product_code='Y' THEN
                SET  v_discount=.04;
            ELSE
                SET  v_discount=.01;
            END IF;
          ELSE
            SET  v_discount=0;
          END IF;
        ELSEIF  v_quantity > 50 THEN
          SET  v_discount=.1;
        . . . More logic . . .
      END IF;

It’s fairly difficult to determine which set of conditions is applied to any particular discount. For instance, consider the highlighted line above—it takes a bit of puzzling to work out which states, quantities, and so on are associated with this discount: and that is with the vast majority of the logic removed. There are a few possible solutions to this deep nesting:

• Including multiple conditions in each IF or ELSEIF clause: For instance, we might test for a specific combination of state, quantity, and status on the one line.

• Removing parts of the logic to separate subroutines: For instance, we might create separate subroutines that calculate discounts for each state.

• Creating a data-driven solution: For instance, in the above example it would probably be preferable to create a table that includes the discount for each combination of values.