Application Development Guide

Returning Result Sets From SQL Procedures

Returning result sets from SQL procedures is similar to returning result sets from external stored procedures. Client applications must use the CLI, JDBC, or SQLJ application programming interfaces to accept result sets from an SQL procedure. SQL procedures that call other SQL procedures also can accept result sets from those procedures. To return a result set from an SQL procedure, write your SQL procedure as follows:

1. Declare the number of result sets that the SQL procedure returns using the DYNAMIC RESULT SETS clause of the CREATE PROCEDURE statement.
2. Declare a cursor using the DECLARE CURSOR statement.
3. Open the cursor using the OPEN CURSOR statement.
4. Exit from the SQL procedure without closing the cursor.

For example, you can write an SQL procedure that returns a single result set, based on the value of the INOUT parameter threshold, as follows:

   CREATE PROCEDURE RESULT_SET (INOUT threshold SMALLINT)
   LANGUAGE SQL
   DYNAMIC RESULT SETS 1
   BEGIN
      DECLARE cur1 CURSOR WITH RETURN TO CALLER FOR
          SELECT name, job, years
          FROM staff
          WHERE years < threshold;
      OPEN cur1;
   END

Returning Result Sets to Caller or Client

If your application returns result sets from nested SQL procedures, you must use the WITH RETURN clause of the DECLARE CURSOR statement to ensure that DB2 returns the result sets to the appropriate location. If a target SQL procedure returns result sets to a calling SQL procedure, the caller must use the ALLOCATE CURSOR and ASSOCIATE RESULT SET LOCATOR statements to access and use the result set.

Returning Result Sets to the Client

To always return a result set from an SQL procedure to a client application, use the WITH RETURN TO CLIENT clause in the DECLARE CURSOR statement associated with the result set. In the following example, the SQL procedure "CLIENT_SET" uses the WITH RETURN TO CLIENT clause in the DECLARE CURSOR statement to return a result set to the client application, even if "CLIENT_SET" is the target of a nested SQL procedure CALL statement:

   CREATE PROCEDURE CLIENT_SET()
   DYNAMIC RESULT SETS 1
   LANGUAGE SQL
   BEGIN
      DECLARE clientcur CURSOR WITH RETURN TO CLIENT
          FOR SELECT name, dept, job
          FROM staff
          WHERE salary > 20000;
      OPEN clientcur;
   END

Returning Result Sets to the Caller

To return a result set to the direct caller of an SQL procedure, whether the caller is a client application or another SQL procedure, use the WITH RETURN TO CALLER clause in the DECLARE CURSOR statement associated with the result set. In the following example, the SQL procedure "CALLER_SET" uses the WITH RETURN TO CALLER clause to return a result set to the caller of CALLER_SET:

   CREATE PROCEDURE CALLER_SET()
   DYNAMIC RESULT SETS 1
   LANGUAGE SQL
   BEGIN
      DECLARE clientcur CURSOR WITH RETURN TO CALLER
          FOR SELECT name, dept, job
          FROM staff
          WHERE salary > 15000;
      OPEN clientcur;
   END

Receiving Result Sets as a Caller

When you expect your calling SQL procedure to receive a result set from a target SQL procedure, you must use the ALLOCATE CURSOR and ASSOCIATE RESULT SET LOCATOR statements to access and use the result set.

ASSOCIATE RESULT SET LOCATOR

After a CALL statement to a target SQL procedure that returns one or more result sets to the caller, your calling SQL procedure should issue this statement to assign result set locator variables for each of the returned result sets. For example, a calling SQL procedure that expects to receive three result sets from a target SQL procedure could contain the following SQL:

   DECLARE result1 RESULT_SET_LOCATOR VARYING;
   DECLARE result2 RESULT_SET_LOCATOR VARYING;
   DECLARE result3 RESULT_SET_LOCATOR VARYING;
 
   CALL targetProcedure();
   ASSOCIATE RESULT SET LOCATORS(result1, result2, result3)
      WITH PROCEDURE targetProcedure;

ALLOCATE CURSOR

Use the ALLOCATE CURSOR statement in a calling SQL procedure to open a result set returned from a target SQL procedure. To use the ALLOCATE CURSOR statement, the result set must already be associated with a result set locator through the ASSOCIATE RESULT SET LOCATORS statement. Once the SQL procedure issues an ALLOCATE CURSOR statement, you can fetch rows from the result set using the cursor name declared in the ALLOCATE CURSOR statement. To extend the previously described ASSOCIATE LOCATORS example, the SQL procedure could fetch rows from the first of the returned result sets using the following SQL:

   DECLARE result1 RESULT_SET_LOCATOR VARYING;
   DECLARE result2 RESULT_SET_LOCATOR VARYING;
   DECLARE result3 RESULT_SET_LOCATOR VARYING;
   CALL targetProcedure();
   ASSOCIATE RESULT SET LOCATORS(result1, result2, result3)
      WITH PROCEDURE targetProcedure;
   ALLOCATE rsCur CURSOR FOR result1;
   WHILE (at_end = 0) DO
      SET total1 = total1 + var1;
      SET total2 = total2 + var2;
      FETCH FROM rsCur INTO var1, var2;
   END WHILE;