CASE statements can be used to conditionally enter into some logic based on the status of a condition being satisfied.
The WHEN clause of the CASE statement defines the value that when satisfied determines the flow of control.
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
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.