IF statement

Description

Provides conditional execution of SQL statements.

Syntax

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

Examples

Example 1

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

Usage

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.

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.

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