IF statement
The IF statement executes different sets of SQL statements based on the result of search conditions.
Syntax
Description
- label
- Specifies the label for the IF statement. The label name cannot be the same as the routine name or another label within the same scope. For more information, see References to SQL labels.
- search-condition
- Specifies the search-condition for which an SQL statement should be executed. 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 an SQL statement to execute if the preceding search-condition is true.
Notes
Considerations for SQLSTATE and SQLCODE SQL variables: When the first SQL-procedure-statement in the IF statement is executed, the SQLSTATE and SQLCODE SQL variables reflect the result of evaluating the search-conditions of that IF statement. If an IF statement does not include an ELSE clause and none of the search-conditions evaluate to true, then when the statement that follows the IF statement is executed, the SQLSTATE and SQLCODE SQL variables reflect the result of evaluating the search conditions of that IF statement.
Example
The following SQL procedure accepts two IN parameters: an employee number and an employee 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
MODIFIES SQL DATA
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