IBM i SQL Stored Procedures : Case When

IBM i SQL Stored Procedures: Case When

In SQL, the CASE statement is often used within stored procedures to handle conditional logic.

It’s similar to the if-else logic found in other programming languages.

Here’s a basic example of how you might use a simple CASE statement within an IBM i stored procedure:

CREATE PROCEDURE UpdateDepartmentName (IN p_deptcode CHAR(3))
LANGUAGE SQL
BEGIN
  -- Variable to hold the department code
  DECLARE v_deptcode CHAR(3);
  SET v_deptcode = p_deptcode;

  -- Simple CASE statement to update the department name based on the code
  CASE v_deptcode
    WHEN 'A00' THEN
      UPDATE department SET deptname = 'Dept A';
    WHEN 'B01' THEN
      UPDATE department SET deptname = 'Dept B';
    ELSE
      UPDATE department SET deptname = 'Other Dept';
  END CASE;
END

In this example, the v_deptcode variable is set to the value of the input parameter p_deptcode. The CASE statement then checks the value of v_deptcode and updates the deptname column in the department table accordingly. If v_deptcode is ‘A00’, it sets the deptname to ‘Dept A’; if it’s ‘B01’, to ‘Dept B’; and for any other value, it sets the deptname to ‘Other Dept’.

Here’s another example of how you might use a CASE statement in a SQL stored procedure:

CREATE PROCEDURE UpdateEmployeeStatus
    @EmpID int,
    @NewStatus nvarchar(10)
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE tblEmployee
    SET Status = CASE @NewStatus
        WHEN 'Active' THEN 'Active'
        WHEN 'Inactive' THEN 'Inactive'
        WHEN 'OnLeave' THEN 'On Leave'
        ELSE 'Unknown'
    END
    WHERE EmpID = @EmpID;
END

In this example, the CASE statement is used to update the status of an employee based on the @NewStatus parameter. If @NewStatus matches one of the specified conditions, the corresponding value is returned and used in the UPDATE statement. If there is no match, the ELSE part is used to return a default value.

Remember that CASE statements can only return a single scalar value and cannot be used to control the flow of execution like an if-else structure can. For more complex conditional logic that involves running different blocks of SQL statements, you would typically use IF...ELSE statements instead.