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

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

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