Application Development Guide

Nested SQL Procedures

Your SQL procedures can contain CALL statements to call other SQL procedures. This feature, called nested stored procedures, enables you to reuse existing SQL procedures and design more complex applications.

Passing Parameters Between Nested SQL Procedures

To call a target SQL procedure from within a caller SQL procedure, simply include a CALL statement with the appropriate number and types of parameters in your caller. If the target returns OUT parameters, the caller can use the returned values in its own statements.

For example, you can create an SQL procedure that calls a target SQL procedure with the name "SALES_TARGET" and that accepts a single OUT parameter of type INTEGER with the following SQL:

   CREATE PROCEDURE NEST_SALES(OUT budget DECIMAL(11,2))
   LANGUAGE SQL
   BEGIN
      DECLARE total INTEGER DEFAULT 0;
      SET total = 6;
      CALL SALES_TARGET(total);
      SET budget = total * 10000;
   END

Returning Result Sets From Nested SQL Procedures

If a target SQL procedure returns result sets, either the caller or the client application receives the result sets, depending on the DECLARE CURSOR statements issued by the target SQL procedure. For each DECLARE CURSOR statement in the target that includes the WITH RETURN TO CLIENT clause, the caller does not receive the result set. For WITH RETURN TO CLIENT cursors, the result set is returned directly to the client application.

For more information on returning result sets from nested SQL procedures, see Returning Result Sets to Caller or Client.

Restrictions on Nested SQL Procedures

Keep the following restrictions in mind when designing your application architecture:

LANGUAGE

SQL procedures can only call stored procedures written in SQL or C. You cannot call other host language stored procedures from within an SQL procedure.

16 levels of nesting

You may only include a maximum of 16 levels of nested calls to SQL procedures. A scenario where SQL procedure A calls SQL procedure B, and SQL procedure B calls SQL procedure C, is an example of three levels of nested calls.

Recursion

You can create an SQL procedure that calls itself recursively. Recursive SQL procedures must comply with the previously described restriction on the maximum levels of nesting.

Security

An SQL procedure cannot call a target SQL procedure that is cataloged with a higher SQL data access level. For example, an SQL procedure created with the CONTAINS SQL clause can call SQL procedures created with either the CONTAINS SQL clause or the NO SQL clause, and cannot call SQL procedures created with either the READS SQL DATA clause or the MODIFIES SQL DATA clause.

An SQL procedure created with the NO SQL clause cannot issue a CALL statement.