FirebirdSQL logo

IF …​ THEN …​ ELSE

Conditional branching

Syntax
IF (<condition>)
  THEN <compound_statement>
  [ELSE <compound_statement>]
Table 1. IF …​ THEN …​ ELSE Parameters
Argument Description

condition

A logical condition returning TRUE, FALSE or UNKNOWN

compound_statement

A single statement, or statements wrapped in BEGIN …​ END

The conditional branch statement IF …​ THEN is used to branch the execution process in a PSQL module.The condition is always enclosed in parentheses.If the condition returns the value TRUE, execution branches to the statement or the block of statements after the keyword THEN.If an ELSE is present, and the condition returns FALSE or UNKNOWN, execution branches to the statement or the block of statements after it.

Multi-Branch Decisions

PSQL does not provide more advanced multi-branch jumps, such as CASE or SWITCH.However, it is possible to chain IF …​ THEN …​ ELSE statements, see the example section below.Alternatively, the CASE statement from DSQL is available in PSQL and is able to satisfy at least some use cases in the manner of a switch:

CASE <test_expr>
  WHEN <expr> THEN <result>
  [WHEN <expr> THEN <result> ...]
  [ELSE <defaultresult>]
END

CASE
  WHEN <bool_expr> THEN <result>
  [WHEN <bool_expr> THEN <result> ...]
  [ELSE <defaultresult>]
END
Example in PSQL
...
C = CASE
      WHEN A=2 THEN 1
      WHEN A=1 THEN 3
      ELSE 0
    END;
...

IF Examples

  1. An example using the IF statement.Assume that the variables FIRST, LINE2 and LAST were declared earlier.

    ...
    IF (FIRST IS NOT NULL) THEN
      LINE2 = FIRST || ' ' || LAST;
    ELSE
      LINE2 = LAST;
    ...
  2. Given IF …​ THEN …​ ELSE is a statement, it is possible to chain them together.Assume that the INT_VALUE and STRING_VALUE variables were declared earlier.

    IF (INT_VALUE = 1) THEN
      STRING_VALUE = 'one';
    ELSE IF (INT_VALUE = 2) THEN
      STRING_VALUE = 'two';
    ELSE IF (INT_VALUE = 3) THEN
      STRING_VALUE = 'three';
    ELSE
      STRING_VALUE = 'too much';

    This specific example can be replaced with a simple CASE or the DECODE function.

WHILE …​ DO

Looping construct

Syntax
[label:]
WHILE (<condition>) DO
  <compound_statement>
Table 1. WHILE …​ DO Parameters
Argument Description

label

Optional label for LEAVE and CONTINUE.Follows the rules for identifiers.

condition

A logical condition returning TRUE, FALSE or UNKNOWN

compound_statement

A single statement, or statements wrapped in BEGIN …​ END

A WHILE statement implements the looping construct in PSQL.The statement or the block of statements will be executed as long as the condition returns TRUE.Loops can be nested to any depth.

WHILE …​ DO Examples

A procedure calculating the sum of numbers from 1 to I shows how the looping construct is used.

CREATE PROCEDURE SUM_INT (I INTEGER)
RETURNS (S INTEGER)
AS
BEGIN
  s = 0;
  WHILE (i > 0) DO
  BEGIN
    s = s + i;
    i = i - 1;
  END
END

Executing the procedure in isql:

EXECUTE PROCEDURE SUM_INT(4);

the result is:

S
==========
10