Navigation:  Advantage SQL > SQL PSM (SCRIPT) >

IF

Advantage SQL Engine

Previous pageReturn to chapter overviewNext page

The IF statement can be used in a script to branch the execution based on a Boolean expression. The optional ELSE or ELSEIF clauses can be used to simulate the CASE … WHEN … control structure type.

Syntax

IF condition_expr THEN

 statement_block

[elseif_clause_list]

[ELSE statement_block]

END IF | END | ENDIF;

 

statement_block :: statement | statement;

condition_expr ::= Boolean | FETCH cursor_name

 

elseif_clause_list ::= ELSEIF condition_expr THEN

 statement_block

[elseif_clause_list]

 

Note that the condition_expr may be a regular Boolean expression or it may be a FETCH statement . The FETCH statement, when used in this context, evaluates to TRUE if the cursor is located on a valid row after the fetch operation; otherwise, it evaluates to FALSE. This provides a convenient method for testing the "end of file" condition when scrolling through a cursor.

Description

The IF statement is similar to the general branching statement in other programming languages. If the condition_expr evaluates to TRUE, the statement_block after the THEN keyword is executed. The statement block is terminated when an ELSE, ELSEIF, ELIF or END keyword is encountered. If the condition_expr evaluates to FALSE, the execution jumps to the elseif_clause_list if present. If no elseif_clause_list is present but there is an ELSE section, the execution jumps to the ELSE section. If there is no elseif_clause_list and no ELSE section, the execution continues to the statement after the END [IF].

The execution of the elseif_clause is the same as the execution of a regular IF statement.

Example 1

// A sample script demonstrates the IF statement

 

DECLARE dVal Double;

 

dVal = 3 * Rand();

 

IF dVal < 1 THEN

 SELECT * FROM employees;

ELSEIF dVal < 2 THEN

 dVal = dVal + 3;

 SELECT * FROM employees WHERE empid > dVal;

ELSE

 UPDATE employees SET empid = empid + dVal;

 dVal = dVal * dVal;

ENDIF;

 

Example 2

// A sample script demonstrates the IF statement using the FETCH result

 

DECLARE cursor1 CURSOR AS SELECT * FROM employees;

 

OPEN cursor1;

 

IF FETCH Cursor1 THEN

 INSERT INTO ErrorLog ( msg ) VALUES ( 'employees is not empty' );

ENDIF;

 

CLOSE cursor1;