IF statement

Use this statement to control conditional execution of SQL statements.

Syntax
IF search-condition THEN statement-list
[ ELSEIF { search-condition | operation-type } THEN statement-list ] ...
[ ELSE statement-list ]
{ END IF | ENDIF }
Remarks

The IF statement is a control statement that allows you to conditionally execute the first list of SQL statements whose search-condition evaluates to TRUE. If no search-condition evaluates to TRUE, and an ELSE clause exists, the statement-list in the ELSE clause is executed.

Execution resumes at the first statement after the END IF.

IF statement is different from IF expression

Do not confuse the syntax of the IF statement with that of the IF expression.

For information about the IF expression, see IF expressions.

Permissions

None.

Side effects

None.

See also
Standards and compatibility
  • SQL/2003   Persistent Stored Module feature.

Example

The following procedure illustrates the use of the IF statement:

CREATE PROCEDURE TopCustomer2 (OUT TopCompany CHAR(35), OUT TopValue INT)
BEGIN
   DECLARE err_notfound EXCEPTION
   FOR SQLSTATE '02000';
   DECLARE curThisCust CURSOR FOR
   SELECT CompanyName, CAST(    sum(SalesOrderItems.Quantity *
   Products.UnitPrice) AS INTEGER) VALUE
   FROM Customers
   LEFT OUTER JOIN SalesOrders
   LEFT OUTER JOIN SalesOrderItems
   LEFT OUTER JOIN Products
   GROUP BY CompanyName;
   DECLARE ThisValue INT;
   DECLARE ThisCompany CHAR(35);
   SET TopValue = 0;
   OPEN curThisCust;
   CustomerLoop:
   LOOP
      FETCH NEXT curThisCust
      INTO ThisCompany, ThisValue;
      IF SQLSTATE = err_notfound THEN
         LEAVE CustomerLoop;
      END IF;
      IF ThisValue > TopValue THEN
         SET TopValue = ThisValue;
         SET TopCompany = ThisCompany;
      END IF;
   END LOOP CustomerLoop;
   CLOSE curThisCust;
END;