User's Guide

Part VI. SQL Anywhere Reference

Chapter 43. Watcom-SQL Statements

Function

Provide conditional execution of SQL statements.

Syntax

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

Usage

Procedures, triggers, and batches.

Permissions

None.

Side effects

None.

See also

• Compound statements
• The chapter "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. If no search-condition evaluates to TRUE, and there is no ELSE clause, the expression returns a NULL value.

Execution resumes at the first statement after the END IF.

Example

     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