IF Statement

Lets you conditionally execute the first list of SQL statements whose search-condition evaluates to TRUE.

Quick Links:

Syntax

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

Examples

(back to top)

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

(back to top)

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.

Note: Do not confuse the syntax of the IF statement with that of the IF expression. You cannot nest the IF statement.

Standards

(back to top)

SQL—ISO/ANSI SQL compliant.
SAP Sybase Database product—The Transact-SQL IF statement has a slightly different syntax.

Permissions

(back to top)

None