DB2 10.5 for Linux, UNIX, and Windows

IF statement

The IF statement selects an execution path based on the evaluation of a condition.

Invocation

This statement can be embedded in an:

SQL procedure definition
Compound SQL (compiled) statement
Compound SQL (inlined) statement

The compound statements can be embedded in an SQL procedure definition, SQL function definition, or SQL trigger definition. It is not an executable statement and cannot be dynamically prepared.

Authorization

Group privileges are not considered because this statement cannot be dynamically prepared.

Syntax


>>-IF--search-condition--THEN--| SQL-routine-statement |-------->

   .---------------------------------------------------------------.   
   V                                                               |   
>----+-----------------------------------------------------------+-+-->
     '-ELSEIF--search-condition--THEN--| SQL-routine-statement |-'     

>--+---------------------------------+--END IF-----------------><
   '-ELSE--| SQL-routine-statement |-'           

SQL-routine-statement

     .----------------------------.        
     V                            |        
|--+---SQL-procedure-statement--;-+----+------------------------|
   | .-------------------------------. |   
   | V                               | |   
   '---| SQL-function-statement |--;-+-'

Description

search-condition: Specifies the condition for which an SQL statement should be invoked. If the condition is unknown or false, processing continues to the next search condition, until either a condition is true or processing reaches the ELSE clause.
SQL-procedure-statement: Specifies the statement to be invoked if the preceding search-condition is true. SQL-procedure-statement is only applicable when in the context of an SQL procedure or a compound SQL (compiled) statement. See SQL-procedure-statement in "Compound SQL (compiled)" statement.
SQL-function-statement: Specifies the statement to be invoked if the preceding search-condition is true. SQL-function-statement is only applicable when in the context of a compound SQL (inlined) statement, an SQL trigger, an SQL function, or an SQL method. See SQL-function-statement in "FOR".

Example

The following SQL procedure accepts two IN parameters: an employee number employee_number and an employee rating rating. Depending on the value of rating, the employee table is updated with new values in the salary and bonus columns.

   CREATE PROCEDURE UPDATE_SALARY_IF
     (IN employee_number CHAR(6), INOUT rating SMALLINT)
     LANGUAGE SQL
     BEGIN
       DECLARE not_found CONDITION FOR SQLSTATE '02000';
       DECLARE EXIT HANDLER FOR not_found
         SET rating = -1;
       IF rating = 1
         THEN UPDATE employee
         SET salary = salary * 1.10, bonus = 1000
         WHERE empno = employee_number;
       ELSEIF rating = 2
         THEN UPDATE employee
         SET salary = salary * 1.05, bonus = 500
         WHERE empno = employee_number;
       ELSE UPDATE employee
         SET salary = salary * 1.03, bonus = 0
         WHERE empno = employee_number;
       END IF;
     END