Returning result sets from SQL procedures

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.

Before you begin

Attention: With the release of Db2 11.5.9, support for Microsoft .Net common language runtime (CLR) routines is deprecated and might be removed in the future. If you are currently running routines that have a dependency on .NET CLR, rewrite the routine logic in a supported language and then recreate the routines.

You must have the authority to create an SQL procedure.

Procedure

To return a result set from an SQL procedure:

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

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
@