CALL/EXECUTE PROCEDURE

For an overview and examples of procedures and triggers, see Procedures and Triggers.

This SQL statement can be executed in the following ways:	Through the CA Datacom Datadictionary Interactive SQL Service Facility (interactive)	In an application program prepared using a CA Datacom/DB SQL Preprocessor (embedded)	By using CA Dataquery (SQL & Batch Modes)
CALL EXECUTE PROCEDURE	YES if no parms passed	YES	YES if no parms passed

Note: YES indicates a valid execution method for this statement.

A CALL or EXECUTE PROCEDURE statement is used to call user-written procedure logic. This statement can be coded directly by the end-user or can be embedded in a trigger definition. Please note that procedures themselves can contain CALL/EXECUTE PROCEDURE statements, subject to the following discussion of nesting limitation.

A procedure can also cause procedures to execute (trigger them) by executing INSERT, UPDATE, or DELETE statements. The depth of nesting of these recursive procedure calls is limited by the PROCEDURE Multi-User startup option that can be used to limit or eliminate nesting.

If null indication variables are passed to the procedure, a list of pointers to the indicators follows the list of pointers to the variables themselves. Although the pointers are contiguous, the indicators themselves are not.

Note: Because SQL has no way of verifying the compatibility of the user-written code with the procedure defined by the CALL/EXECUTE PROCEDURE statement, it is the sole responsibility of the creator of the procedure to ensure that the CALL/EXECUTE PROCEDURE statement precisely reflects the parameter list expected by the user-written program. Failure to properly coordinate parameter lists can cause the procedure subtask to abnormally terminate.

►►─┬─ CALL ──────────────┬─ proc-name ─ proc-parms ───────────────────────────►◄
   └─ EXECUTE PROCEDURE ─┘
►►─┬─────────────────────────────────────────────────┬────────────────────────►◄
   └─ ( ─┬────────────────────────────────────┬ ─ ) ─┘
         ├─ positional-parms ─────────────────┤
         ├─ keyword-parms ────────────────────┤
         └─ positional-parms , keyword-parms ─┘
   ┌─ , ──────────────────────────┐
►►─▼─ parameter-value-expression ─┴───────────────────────────────────────────►◄
   ┌─ , ─────────────────────────────────────────┐
►►─▼─ parameter-name=parameter-value-expression ─┴────────────────────────────►◄
►►─┬─ literal ──────────────────┬─────────────────────────────────────────────►◄
   ├─ host-variable ────────────┤
   ├─ BYREF(host-variable) ─────┤
   ├─ value-expression ─────────┤
   └─ trigger-column-reference ─┘

CALL

(Required)

EXECUTE PROCEDURE

(Required)

proc-name

Specify a procedure name. The procedure name cannot be specified as a host variable reference.

ANSI supports overloading of the procedure name, with duplicate procedure names resolved to procedure definitions using the layout of the parameter list. For syntax compatibility purposes, a second, specific name may be given to a procedure to uniquely identify it, but it must match the nonspecific name, and the nonspecific name must be unique.

parameter-name

Enter the name of the parameter as specified in the CREATE PROCEDURE statement.

literal

Specify a literal (value) for the parameter.

host-variable

Specify a host-variable by naming a variable that is described in the program in accordance with the rules for declaring host variables.

BYREF(host-variable)

This clause, supported for compatibility purposes, is treated as a host variable reference.

value-expression

Specify a value expression (value expressions are used in procedure parameter lists).

trigger-column-reference

(Valid only in a CREATE TRIGGER or CREATE RULE statement.) A reference to a base table column value through a correlation name defined in the old-row/new-row syntax of the CREATE TRIGGER/CREATE RULE statements.