Passing Parameters Between Nested SQL Procedures

As with procedures in any programming language, nested SQL procedures are not of much use unless you can pass values between the caller and the called procedures. In the examples shown in Figure 8.1 and Figure 8.2, you can pass values in and out by issuing the CALL statement in the caller procedure. The number of parameters and their data types must match the called procedure signature. After the successful completion of a CALL statement, the OUT parameter values will be available to the caller procedure.

Tip

DB2 SQL PL uses strong data typing. This means that you will have to match the data types of the local variables with those of the parameters in the called procedure's signature, if possible. You will have to use the explicit CAST functions if the specified data type does not support implicit conversion.

The local variables are matched to the called SQL procedure parameters by their positions in the CALL statement.

If you have overloaded the called SQL procedure, DB2 will determine which procedure to invoke by the number of parameters in the CALL statement. Overloading with the same number of parameters is not supported, even if the data types are different. Overloading of procedures is not possible on zSeries. For detailed information on overloaded SQL procedures, refer to Chapter 2, "Basic SQL Procedure Structure."