FirebirdSQL logo
 Data Manipulation (DML) StatementsBuilt-in Scalar Functions 

BEGIN …​ END Examples

A sample procedure from the employee.fdb database, showing simple usage of BEGIN …​ END blocks:
SET TERM ^;
CREATE OR ALTER PROCEDURE DEPT_BUDGET (
  DNO CHAR(3))
RETURNS (
  TOT DECIMAL(12,2))
AS
  DECLARE VARIABLE SUMB DECIMAL(12,2);
  DECLARE VARIABLE RDNO CHAR(3);
  DECLARE VARIABLE CNT  INTEGER;
BEGIN
  TOT = 0;

  SELECT BUDGET
  FROM DEPARTMENT
  WHERE DEPT_NO = :DNO
  INTO :TOT;

  SELECT COUNT(BUDGET)
  FROM DEPARTMENT
  WHERE HEAD_DEPT = :DNO
  INTO :CNT;

  IF (CNT = 0) THEN
    SUSPEND;

  FOR SELECT DEPT_NO
    FROM DEPARTMENT
    WHERE HEAD_DEPT = :DNO
    INTO :RDNO
  DO
  BEGIN
    EXECUTE PROCEDURE DEPT_BUDGET(:RDNO)
      RETURNING_VALUES :SUMB;
    TOT = TOT + SUMB;
  END

  SUSPEND;
END^
SET TERM ;^
See also

[fblangref50-psql-exit], SET TERM

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;
...