The IF statement executes statements depending on certain conditions. It may may only be specified in a routine, i.e. in the context of a CREATE PROCEDURE or CREATE FUNCTION statement. Routines and their use in SESAM/SQL are described in detail in chapter "Routines".
The IF statement is a non-atomic SQL statement, i.e. further (atomic or non-atomic) SQL statements can occur in it.
IF search_condition
THEN routine_sql_statement; [ routine_sql_statement; ]...
[ELSEIF search_condition THEN routine_sql_statement; [ routine_sql_statement; ]...]...
[ELSE routine_sql_statement; [ routine_sql_statement; ]... ]
END IF
search_condition
Search condition that returns a truth value when evaluated
The search condition may contain parameters of routines and (if the statement is part of a COMPOUND statement) local variables, but no host variables.
A column may be specified only if it is part of a subquery
routine_sql_statement
SQLstatement which is to be executed in the THEN or ELSE part of the IF statement. An SQL statement is concluded with a ";" (semicolon).
Multiple SQL statements can be specified one after the other. They are executed in the order specified.
No privileges are checked before an SQL statement is executed.
An SQL statement in a routine may access the parameters of the routine and (if the statement is part of a COMPOUND statement) local variables, but not host variables.
The syntax and meaning of routine_sql_statement are described centrally in section "SQL statements in routines". The SQL statements named there may not be used.
Execution information
The IF and ELSEIF clauses are processed from left to right. The associated SQL statements are processed for the first THEN clause whose search condition returns the truth value true. The IF statement is then terminated.
If none of the search conditions returns the truth value true and an ELSE clause exists, the SQL statements of the ELSE clause are processed.
SQL statements are not processed if the associated search condition returns the truth value unknown.
The IF statement is a non-atomic statement:
If the IF statement is part of a COMPOUND statement, the rules described there apply, in particular the exception routines defined there.
If the IF statement is not part of a COMPOUND statement and one of the SQL statements reports an SQLSTATE, it is possible that only the updates of this SQL statement will be undone. The IF statement and the routine in which it is contained are aborted. The SQL statement in which the routine was used returns the SQLSTATE concerned.
Example
The SQL statements are executed only if the tab table is not empty.
IF (SELECT COUNT(*) FROM tab) > 0 THEN routine_sql_statement END IF
See also
CREATE PROCEDURE, CREATE FUNCTION