Reference Manual
   CHAPTER 9. SQL Statements     

IF statement

Function 

Provide conditional execution of SQL statements.

Syntax 

IF search-condition THEN statement-list
... [ ELSEIF search-condition THEN statement-list ] ...
... [ ELSE statement-list ]
... END IF

Permissions 

None.

Side effects 

None.

See also 

BEGIN... END statement

Using Procedures, Triggers, and Batches

Description 

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.

Standards and compatibility 

• SQL/92     Persistent Stored Module feature.

• Sybase     The Transact-SQL IF statement has a slightly different syntax.

Example 

• The following procedure illustrates the use of the IF statement:

  CREATE PROCEDURE TopCustomer (OUT TopCompany CHAR(35),                               OUT TopValue INT)
  BEGIN
     DECLARE err_notfound EXCEPTION
     FOR SQLSTATE '02000' ;
     DECLARE curThisCust CURSOR FOR
     SELECT company_name, CAST(    sum(sales_order_items.quantity *
     product.unit_price) AS INTEGER) VALUE
     FROM customer
     LEFT OUTER JOIN sales_order
     LEFT OUTER JOIN sales_order_items
     LEFT OUTER JOIN product
     GROUP BY company_name ;
  
     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