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 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.

None.

Standards

Permissions

None.

See also