IF

Remarks

IF statements provide conditional execution based on the value of a condition. The IF . . . THEN . . . [ELSE . . .] construct controls flow based on which of two statement blocks will be executed.

You may use IF statements in the body of both stored procedures and triggers.

There is no limit to the number of nested IF statements allowed, although the query remains subject to the usual total length limitation and other applicable limitations.

Examples

The following example uses the IF statement to set the variable Negative to either 1 or 0, depending on whether the value of vInteger is positive or negative.

IF (:vInteger < 0) THEN 
SET :Negative = '1'; 
ELSE 
SET :Negative = '0'; 
END IF; 

The following example uses the IF statement to test the loop for a defined condition (SQLSTATE = '02000'). If it meets this condition, then the WHILE loop is terminated.

FETCH_LOOP: 
WHILE (:counter < :NumRooms) DO 
FETCH NEXT FROM cRooms into :CurrentCapacity; 
IF (SQLSTATE = '02000') THEN 
LEAVE FETCH_LOOP; 
END IF; 
SET :counter = :counter + 1; 
SET :TotalCapacity = :TotalCapacity + 
:CurrentCapacity; 
END WHILE; 

See Also

CREATE PROCEDURE

CREATE TRIGGER