Invoking a Stored Procedure

Invoking 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.

There are three types of CALL statements which need to be addressed since DB2® SQL for iSeries has different rules for each type. They are:

* 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

Note:
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.

Following is a discussion of each type.

* Using CALL statement where procedure definition exists
* Using embedded CALL statement where no procedure definition exists
* Using Embedded CALL statement with an SQLDA
* Using dynamic CALL statement where no CREATE PROCEDURE exists

Additionally, you can find more examples at Examples of CALL statements.