IF

The IF statement is used to allow conditional execution of one or more statements within a procedure.

Scope

Procedures only

SQL Syntax

  IF Condition THEN [Statement;[...]] 
  [ELSEIF Condition THEN [Statement; [...]]] 
  [ELSE [Statement; [...]]] ENDIF;

Parameters

Condition: specifies anything that is allowed in a search condition except subqueries, column references, host variables, dynamic parameters, aggregate functions, string functions, date/time functions involving column references, long column functions, or TID functions. Local variables, built-in variables, and parameters may be included. See Chapter 9 “Search Conditions” for more information.
Statement: is any statement allowed in a procedure, including a compound statement. The statement may also be empty.

Description

IF statements can be nested. In a nested IF statement, each ELSE is associated with the closest preceding IF.
Local variables, and parameters can be used anywhere a host variable would be allowed.
Each Statement may be a single simple statement, a compound statement, or empty.

Authorization

Anyone can use the IF statement.

Example

Create a procedure to enter orders into different tables according to the size of the order:

   CREATE PROCEDURE OrderEntry (PartName CHAR(20) NOT NULL,
         Quantity INTEGER NOT NULL) AS
      BEGIN
         IF :Quantity < 100 THEN
             INSERT INTO SmallOrders 
             VALUES (:PartName, :Quantity);
         ELSE
             INSERT INTO LargeOrders
             VALUES (:PartName, :Quantity);
         ENDIF;
      END

Execute the procedure with different parameters. The first execution adds a row to the LargeOrders table.

EXECUTE PROCEDURE Reorder ('Widget', 1500)

The second execution adds a row to the SmallOrders table.

EXECUTE PROCEDURE Reorder ('Widget', 15)

IF

Technical documentation

» Table of Contents