CASE statement in SQL procedures

CASE statements can be used to conditionally enter into some logic based on the status of a condition being satisfied.

There are two types of CASE statements:

Simple case statement: used to enter into some logic based on a literal value
Searched case statement: used to enter into some logic based on the value of an expression

The WHEN clause of the CASE statement defines the value that when satisfied determines the flow of control.

Here is an example of an SQL procedure with a CASE statement with a simple-case-statement-when-clause:

  CREATE PROCEDURE UPDATE_DEPT (IN p_workdept)
  LANGUAGE SQL
  BEGIN

    DECLARE v_workdept CHAR(3);
    SET v_workdept = p_workdept;

    CASE  v_workdept
      WHEN 'A00' THEN
        UPDATE department SET deptname = 'D1';
      WHEN 'B01' THEN
        UPDATE department SET deptname = 'D2';
      ELSE
        UPDATE department SET deptname = 'D3';
      END CASE

  END

Here is an example of CASE statement with a searched-case-statement-when-clause:

  CREATE PROCEDURE UPDATE_DEPT (IN p_workdept)
  LANGUAGE SQL
  BEGIN

    DECLARE v_workdept CHAR(3);
    SET v_workdept = p_workdept;

    CASE
      WHEN v_workdept = 'A00' THEN
        UPDATE department SET deptname = 'D1';
      WHEN v_workdept = 'B01' THEN
        UPDATE department SET deptname = 'D2';
      ELSE
        UPDATE department SET deptname = 'D3';
    END CASE

  END

The previous two examples are logically equivalent, however it is important to note that CASE statements with a searched-case-statement-when-clause can be very powerful. Any supported SQL expression can be used here. These expressions can contain references to variables, parameters, special registers, and more.