SQL Reference

IF Statement

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

Syntax

>>-IF--search-condition--THEN----------------------------------->
 
      .-------------------------------.
      V                               |
>--------SQL-procedure-statement--;---+------------------------->
 
      .--------------------------------------------------------------------------.
      V                                                                          |
>--------+--------------------------------------------------------------------+--+>
         |                                 .-------------------------------.  |
         |                                 V                               |  |
         '-ELSEIF--search-condition--THEN-----SQL-procedure-statement--;---+--'
 
>-----+------------------------------------------+--END IF-----><
      |       .-------------------------------.  |
      |       V                               |  |
      '-ELSE-----SQL-procedure-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. If no search-condition evaluates to true, then the SQL-procedure-statement following the ELSE keyword is invoked.

Examples

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