DB2 Version 9.7 for Linux, UNIX, and Windows

Returning result sets from SQL procedures

About this task

To return a result set from an SQL procedure, you must:

Before you begin

In SQL procedures, cursors can be used to do more than iterate through rows of a result set. They can also be used to return result sets to the calling program. Result sets can be retrieved by SQL procedures (in the case of a nested procedure calls) or client applications programmed in C using the CLI application programming interface, Java™, CLI, or .NET CLR languages.

Prerequisites

Authority to create an SQL procedure

Procedure

Specify the DYNAMIC RESULT SETS clause in the CREATE PROCEDURE statement
DECLARE the cursor using the WITH RETURN clause
Open the cursor in the SQL procedure
Keep the cursor open for the client application - do not close it

Results

Example

Here is an example of an SQL procedure that only returns a single result set:

  CREATE PROCEDURE  read_emp()
  SPECIFIC read_emp
  LANGUAGE SQL 
  DYNAMIC RESULT SETS 1

  Re:  BEGIN

    DECLARE c_emp CURSOR WITH RETURN FOR
      SELECT salary, bonus, comm.
      FROM employee
      WHERE job != 'PRES';

    OPEN c_emp;

  END Re

If the cursor is closed using the CLOSE statement prior to the return of the SQL procedure, the cursor result set will not be returned to the caller or client application.

Multiple result sets can be returned from an SQL procedure by using multiple cursors. To return multiple cursors the following must be done:

Specify the DYNAMIC RESULT SETS clause in the CREATE PROCEDURE statement. Specify the maximum possible number of result sets likely to be returned. The number of results sets actually returned must not exceed this number.
Declare cursors for each of the result sets to be returned that specify the WITH RETURN clause.
Open the cursors to be returned.
Keep the cursor open for the client application - do not close them.

One cursor is required per result set that is to be returned.

Result sets are returned to the caller in the order in which they are opened.

Once you have created the SQL procedure that returns a result set you might want to call it and retrieve the result set.

Multiple result sets can also be returned by enabling multiple instances of a same cursor. You must DECLARE the cursor using the WITH RETURN TO CLIENT.

An example to enable multiple instances of an open cursor using the WITH RETURN TO CLIENT:

CREATE PROCEDURE PROC(IN a INT)
BEGIN
  DECLARE index INTEGER DEFAULT 1;
  WHILE index < a DO
    BEGIN 
      DECLARE cur CURSOR WITH RETURN TO CLIENT FOR SELECT * FROM T WHERE pk = index;
      OPEN cur;
      SET index = index + 1;
    END;
  END WHILE;
END
@