DB2 Version 9.7 for Linux, UNIX, and Windows

IF statement in SQL procedures

IF statements can be used to conditionally enter into some logic based on the status of a condition being satisfied. The IF statement is logically equivalent to a CASE statements with a searched-case-statement-when clause.

The IF statement supports the use of optional ELSE IF clauses and a default ELSE clause. An END IF clause is required to indicate the end of the statement.

Here is an example of procedure that contains an IF statement:

  CREATE PROCEDURE UPDATE_SAL (IN empNum CHAR(6), 
                               INOUT rating SMALLINT)
  LANGUAGE  SQL
  BEGIN
    IF rating = 1 THEN
      UPDATE employee 
      SET salary = salary * 1.10, bonus = 1000     
        WHERE empno = empNum;
    ELSEIF rating = 2 THEN
      UPDATE employee 
      SET salary = salary * 1.05, bonus = 500 
        WHERE empno = empNum;
    ELSE
      UPDATE employee 
      SET salary = salary * 1.03, bonus = 0 
        WHERE empno = empNum;
    END IF;
  END