MySQL Stored Procedure Programming

Name

IF-01: Use ELSEIF with mutually exclusive clauses

Synopsis

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.