CALL executes a procedure CALL can also be used in a routine to execute another procedure (nested calls of routines).
The CALL statement is a non-atomic SQL statement, as non-atomic statements can be contained in the called procedure.
Procedures and their use in SESAM/SQL are described in detail in chapter "Routines".
You can ascertain which routines are defined and which routines use each other in the views for routines of the INFORMATION_SCHEMA (see chapter "Information schemas").
When a procedure expects input parameters, the corresponding values (arguments) must be transferred to the procedure in the CALL statement.
Output values of procedures which are called outside a routine are stored in corresponding host variables or in the SQL descriptor area. Output values of procedures which are called in a higher-level routine are entered in output parameters or in local variables of the higherranking procedure.
The DEBUG ROUTINE, DEBUG VALUE, and LOOP LIMIT pragmas can also be used. See section "Pragmas and annotations".
They are interpreted only when they are located ahead of a CALL statement which is called externally (in other words from an application), and they then propagate their effect to all directly or indirectly contained CALL statements and User Defined Functions. They have no effect ahead of a CALL statement in a procedure.
Pragmas for optimization can also be specified in a procedure in the case of a CALL statement. They then have an effect on optimizing the call values.
In order to execute a procedure, the current authorization identifier requires the EXECUTE privilege for the procedure to be executed, but not the privileges which are required to execute the DML statements contained in the procedure. In addition, the SELECT privileges for the tables which are addressed in the routine’s call parameters by means of subqueries are required.
CALL procedure arguments
procedure ::= routine
arguments ::= ([ expression [{, expression }...]])
procedure
Name of the procedure to be executed. You can qualify the procedure name with a database and schema name.
([ expression [{, expression }...]])
List of arguments. The number of arguments must be the same as the number of parameters in the procedure definition. The order of the arguments must correspond to that of the parameters. If no parameter is defined for the procedure, the list consists only of the parentheses.
If the nth parameter is of the type IN or INOUT, it is assigned the value of the nth argument before the procedure is executed.
If the nth parameter is of the type OUT or INOUT, the following applies:
If the CALL statement is static, the nth argument must be a host variable (possibly with indicator variable).
The same host variable may not be used as an argument for more than one parameter of the type OUT or INOUT.If the CALL statement is dynamic, the nth argument must be a placeholder ("?").
After the procedure has been executed, the values for the parameters of the type OUT or INOUT are transferred to the corresponding host variables or to an SQL descriptor area.
The data type of the nth argument must be compatible with the data type of the nth parameter. For input parameters, see the information in section "Supplying input parameters for routines". For output parameters, see section "Entering values in a procedure parameter (output) or local variable".
When, in the case of a static SQL statement, a parameter is specified as a host variable, while pre-assembling (without database contact) SESAM/SQL assumes that a parameter of the type IN or INOUT is concerned and transfers this value to the DBH. Even if a pure output parameter is concerned, the value must therefore either be correctly initialized according to the data type or the host variable will be assigned an indicator variable which must then be supplied with the value -1.
CALL and transaction management
CALL introduces an SQL transaction for procedures which are called outside a routine when no transaction is open. As a procedures contains only DML statements, CALL initiates an SQL transaction for data manipulation.
The procedure statements run at the same isolation level and in the same transaction mode as the CALL statement (see section "SET TRANSACTION - Define transaction attributes" ).
When the transaction mode READ ONLY is set, the procedure may not contain any SQL statements for updating data.
CALL and time functions
If the time functions CURRENT_DATE, CURRENT_TIME(3), LOCALTIME(3), CURRENT_TIMESTAMP(3) and LOCALTIMESTAMP(3) are included in a statement multiple times, they are avaluated simultaneously, see section "Time functions". This information also applies for procedure statements. However, this does not mean that the time functions of all statements of a procedure run are evaluated simultaneously:
The time functions of the CALL statement are evaluated simultaneously if they occur as a value in input parameters.
The time functions of each procedure statement are evaluated simultaneously and separately. Different procedure statements consequently generally return different time values.
The time functions of the COMPOUND statement are evaluated simultaneously when they occur as a default value in variable definitions.
The time functions of an IF statement are evaluated simultaneously for all search conditions, both in the IF and in the ELSIF branch. However, the time functions of the procedure statements in the THEN and ELSE branches of the IF statement are once again evaluated simultaneously and separately.
The time functions in cursor descriptions of local cursors are evaluated simultaneously in the OPEN statement for the cursor.
Example
The GetCurrentYear procedure (see "CREATE PROCEDURE - Create procedure") is called.
CALL ProcSchema.GetCurrentYear (OUT myvar)
See also
CREATE PROCEDURE, DROP PROCEDURE