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.