May 1


IBM i SQL Stored Procedures : Case When

By NickLitten

May 1, 2020

SQL, Stored Procedures

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))
  -- 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';
      UPDATE department SET deptname = 'Other Dept';

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)

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

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.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Join the IBM i Community for FREE Presentations, Lessons, Hints and Tips