Summary

This chapter covered nested SQL procedures. Data is passed between the procedures and external applications via parameters or result sets. Output parameters are used to return single values, while result sets are used to return rows of data. The return status, which is of integer type, should be reserved as an execution status indicator only.

You have two options in returning result sets. By using the TO CALLER or TO CLIENT clauses, you can control the destination and the visibility of the result sets. Use TO CLIENT (not supported on zSeries) if the result set is meant for an external application only. Use TO CALLER if the result set is meant for direct callers.

Recursive SQL procedures are special cases of nested SQL procedures. Dynamic SQL is required to implement recursive procedures on LUW.