FOR Statement

Control Statements

SIGNAL Statement

CALL Statement - Calling a Procedure

procedure_call ::= CALL [DETACHED] [scheme.]name ([actual_parameter {, actual_parameter} … ]);
actual_parameter ::= key_parameter | pivotal_parameter
key_parameter ::= formal_parameter_name => expression
pivotal_parameter ::= expression

The CALL statement calls a procedure created by the CREATE PROCEDURE or DECLARE PROCEDURE statement. It can also be used to execute the following API functions: SQL_execute, Log_write, Free_deleted, or Set_sql_option.

It is possible to call a stored procedure from a different application, just prefix the procedure name with the application name and a dot. WARNING: procedures are compiled in the application context where it is stored, not in the calling application context. If the procedure calls a table not prefixed with the application name, then the table in the application where the procedure is stored will be used.

If the DETACHED keyword is specified, the procedure will run in a separate thread. Local or standard procedures may be run DETACHED.

Pivotal actual parameters are assigned to formal parameters specified in the procedure description based on its position (from left to right), while the key parameters are distinguished by the formal parameter name. Pivotal parameters must be specified before the key parameters.

The value of the IN parameter is set by the expression value. The type of the expression must correspond with the formal parameter. The OUT or IN-OUT actual parameter must be a variable or a column of the same type as the formal IN parameter.

602SQL Variations

• Pivotal parameters can be skipped only if a comma is written instead of the parameter (if there are other parameters) or a parenthesis (if it is the last parameter)

SQL Properties in 602SQL Defined by Implementation

• An unspecified IN parameter that has no default value defined will have a NULL value. An unspecified OUT parameter is not written anywhere

Example:

Let's have a procedure called PARAM with one IN parameter and one OUT parameter defined as follows:

PROCEDURE Param( IN dt DATE, OUT res INT );
BEGIN
  SELECT COUNT(*) INTO res
  FROM Tab1
  WHERE dat = dt;
END

This procedure may be called from the 602SQL Client using the pivotal parameters, if we have the following variables defined : date (DATE type) and amount (INT type).

CALL Param(:<date, :>amount);

or using the key parameters:

CALL Param(res=>:>amount, dt=>:<date);

FOR Statement

Control Statements

SIGNAL Statement