FirebirdSQL logo

EXIT

Terminates execution of a module

Syntax
EXIT;

The EXIT statement causes execution of the current PSQL module to jump to the final END statement from any point in the code, thus terminating the program.

Calling EXIT in a function will result in the function returning NULL.

EXIT Examples

Using the EXIT statement in a selectable procedure
CREATE PROCEDURE GEN_100
  RETURNS (I INTEGER)
AS
BEGIN
  I = 1;
  WHILE (1=1) DO
  BEGIN
    SUSPEND;
    IF (I=100) THEN
      EXIT;
    I = I + 1;
  END
END

SUSPEND

Passes output to the buffer and suspends execution while waiting for caller to fetch it

Syntax
SUSPEND;

The SUSPEND statement is used in selectable stored procedures to pass the values of output parameters to a buffer and suspend execution.Execution remains suspended until the calling application fetches the contents of the buffer.Execution resumes from the statement directly after the SUSPEND statement.In practice, this is likely to be a new iteration of a looping process.

Important
Important Notes
  1. The SUSPEND statement can only occur in stored procedures or sub-procedures

  2. The presence of the SUSPEND keyword defines a stored procedure as a selectable procedure

  3. Applications using interfaces that wrap the API perform the fetches from selectable procedures transparently.

  4. If a selectable procedure is executed using EXECUTE PROCEDURE, it behaves as an executable procedure.When a SUSPEND statement is executed in such a stored procedure, it is the same as executing the EXIT statement, resulting in immediate termination of the procedure.

  5. SUSPEND“breaks” the atomicity of the block in which it is located.If an error occurs in a selectable procedure, statements executed after the final SUSPEND statement will be rolled back.Statements that executed before the final SUSPEND statement will not be rolled back unless the transaction is rolled back.

SUSPEND Examples

Using the SUSPEND statement in a selectable procedure
CREATE PROCEDURE GEN_100
  RETURNS (I INTEGER)
AS
BEGIN
  I = 1;
  WHILE (1=1) DO
  BEGIN
    SUSPEND;
    IF (I=100) THEN
      EXIT;
    I = I + 1;
  END
END

EXECUTE STATEMENT

Executes dynamically created SQL statements

Syntax
<execute_statement> ::= EXECUTE STATEMENT <argument>
  [<option> ...]
  [INTO <variables>];

<argument> ::= <paramless_stmt>
            | (<paramless_stmt>)
            | (<stmt_with_params>) (<param_values>)

<param_values> ::= <named_values> | <positional_values>

<named_values> ::= <named_value> [, <named_value> ...]

<named_value> ::= [EXCESS] paramname := <value_expr>

<positional_values> ::= <value_expr> [, <value_expr> ...]

<option> ::=
    WITH {AUTONOMOUS | COMMON} TRANSACTION
  | WITH CALLER PRIVILEGES
  | AS USER user
  | PASSWORD password
  | ROLE role
  | ON EXTERNAL [DATA SOURCE] <connection_string>

<connection_string> ::=
  !! See <filespec> in the CREATE DATABASE syntax !!

<variables> ::= [:]varname [, [:]varname ...]
Table 1. EXECUTE STATEMENT Statement Parameters
Argument Description

paramless_stmt

Literal string or variable containing a non-parameterized SQL query

stmt_with_params

Literal string or variable containing a parameterized SQL query

paramname

SQL query parameter name

value_expr

SQL expression resolving to a value

user

Username.It can be a string, CURRENT_USER or a string variable

password

Password.It can be a string or a string variable

role

Role.It can be a string, CURRENT_ROLE or a string variable

connection_string

Connection string.It can be a string literal or a string variable

varname

Variable

The statement EXECUTE STATEMENT takes a string parameter and executes it as if it were a DSQL statement.If the statement returns data, it can be passed to local variables by way of an INTO clause.

EXECUTE STATEMENT can only produce a single row of data.Statements producing multiple rows of data must be executed with [fblangref50-psql-forexec].