Calling a stored procedure

 

The SQL CALL statement calls a stored procedure.

On the CALL statement, the name of the stored procedure and any arguments are specified. Arguments may be constants, special registers, or host variables. The external stored procedure specified in the CALL statement does not need to have a corresponding CREATE PROCEDURE statement. Programs created by SQL procedures can only be called by invoking the procedure name specified on the CREATE PROCEDURE statement.

Although procedures are system program objects, using the CALL CL command will not typically work to call a procedure. The CALL CL command does not use the procedure definition to map the input and output parameters, nor does it pass parameters to the program using the procedure's parameter style.

The following types of CALL statements need to be addressed because DB2^® UDB for iSeries™ has different rules for each type:

• Embedded or dynamic CALL statement where a procedure definition exists

• Embedded CALL statement where no procedure definition exists

• Dynamic CALL statement where no CREATE PROCEDURE exists

Notes:

Dynamic here refers to:

• A dynamically prepared and executed CALL statement.

• A CALL statement issued in an interactive environment (for example, through STRSQL or Query Manager).

• A CALL statement executed in an EXECUTE IMMEDIATE statement.

• Using the CALL statement where procedure definition exists
  This type of CALL statement reads all the information about the procedure and the argument attributes from the CREATE PROCEDURE catalog definition.

• Using the embedded CALL statement where no procedure definition exists
  A static CALL statement without a corresponding CREATE PROCEDURE statement is processed with these rules.

• Using the embedded CALL statement with an SQLDA
  In either type of embedded CALL statement (where a procedure definition might or might not exist), an SQLDA rather than a parameter list can be passed.

• Using the dynamic CALL statement where no CREATE PROCEDURE exists
  These rules pertain to the processing of a dynamic CALL statement when there is no CREATE PROCEDURE definition.

• Examples: CALL statements
  These examples show how the arguments of a CALL statement are passed to a procedure for several languages, and how the arguments are received into local variables in the procedure.

 

Parent topic:

Stored procedures