IF Statement

Syntax

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

Examples

• Example 1 – This 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 CompanyName, CAST( 	sum(SalesOrderItems.Quantity *
  	Products.UnitPrice) AS INTEGER) VALUE
  	FROM Customers
  	LEFT OUTER JOIN SalesOrders
  	LEFT OUTER JOIN SalesOrsderItems
  	LEFT OUTER JOIN Product
  	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

• Example 2 – This procedure illustrates the use of the ELSEIF statement:

  BEGIN
   DECLARE X INT;
   SET X = 1;
   IF X = 1 THEN
    PRINT '1';
    ELSEIF X = 2 THEN
     PRINT '2';
    ELSE
     PRINT 'something else';
   ENDIF
  END
  

Usage

The IF statement lets you 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.

When comparing variables to the single value returned by a SELECT statement inside an IF statement, you must first assign the result of the SELECT to another variable.

Standards

Permissions

None