SET RESULT SETS

Start of change The SET RESULT SETS statement specifies the result sets that can be returned from a procedure. End of change

Invocation

Start of change This statement can only be embedded in an application program or SQL procedure. It is an executable statement that cannot be dynamically prepared. It is not allowed in a Java™ or REXX procedure. End of change

Authorization

None required.

Syntax


                                   .-TO CALLER-.     
                    .-WITH RETURN--+-----------+-.   
                    |              '-TO CLIENT-' |   
>>-SET RESULT SETS--+----------------------------+-------------->

     .-,----------------------------------------------------.     
     V                                                      |     
>--+---+-ARRAY--host-structure-array--FOR--variable--ROWS-+-+-+-><
   |   '-CURSOR--cursor-name------------------------------'   |   
   '-NONE-----------------------------------------------------'

Description

WITH RETURN

Specifies that the result table of the cursor is intended to be used as a result set that will be returned from a procedure.

For non-scrollable cursors, the result set consists of all rows from the current cursor position to the end of the result table. For scrollable cursors, the result set consists of all rows of the result table.

TO CALLER: Specifies that the cursor can return a result set to the caller of the procedure. For example, if the caller is a client application, the result set is returned to the client application.
TO CLIENT: Specifies that the cursor can return a result set to the client application. This cursor is invisible to any intermediate nested procedures. If a function or trigger called the procedure either directly or indirectly, result sets cannot be returned to the client and the cursor will be closed after the procedure finishes.

CURSOR cursor-name

Identifies a cursor to be used to define a result set that can be returned from a procedure. The cursor-name must identify a declared cursor as explained in Description for the DECLARE CURSOR statement. When the SET RESULT SETS statement is executed, the cursor must be in the open state. It cannot be an allocated cursor. End of change

ARRAY host-structure-array

host-structure-array identifies an array of host structures defined in accordance with the rules for declaring host structures. The array cannot contain a C NUL-terminated host variable.

The first structure in the array corresponds to the first row of the result set, the second structure in the array corresponds to the second row of the result set, and so on. In addition, the first value in the row corresponds to the first item in the structure, the second value in the row corresponds to the second item in the structure, and so on.

Start of change LOBs and XML cannot be returned in an array when using DRDA. End of change

Only one array can be specified in a SET RESULT SETS statement, including any RETURN TO CLIENT array result sets from nested calls to procedures.

FOR variable ROWS

Specifies the number of rows in the result set. The variable must be a numeric variable with zero scale, and it must not include an indicator variable. It must not be a global variable. The number of rows specified must be in the range of 0 to 32767 and must be less than or equal to the dimension of the host structure array. End of change

NONE

Specifies that no result sets will be returned. Cursors left open when the procedure ends will not be returned.

Notes

For more information about result sets, see Result sets from procedures and WITH RETURN clause.

External procedures: There are three ways to return result sets from an external procedure:

If a SET RESULT SETS statement is executed in the procedure, the SET RESULT SETS statement identifies the result sets. The result sets are returned in the order specified on the SET RESULT SETS statement.
If a SET RESULT SETS statement is not executed in the procedure,
- If no cursors have specified a WITH RETURN clause, each cursor that the procedure opens and leaves open when it returns identifies a result set. The result sets are returned in the order in which the cursors are opened.
- If any cursors have specified a WITH RETURN clause, each cursor that is defined with the WITH RETURN clause that the procedure opens and leaves open when it returns identifies a result set. The result sets are returned in the order in which the cursors are opened.

When a result set is returned using an open cursor, the rows are returned starting with the current cursor position.

The RESULT SETS clause should be specified on the ALTER PROCEDURE (External), CREATE PROCEDURE (External) statement, or DECLARE PROCEDURE statement to return result sets from a procedure. The maximum number of result sets returned cannot be larger than the number specified on the ALTER PROCEDURE (External), CREATE PROCEDURE (External) statement, or DECLARE PROCEDURE statement.

SQL procedures: In order to return result sets from an SQL procedure, the procedure must be created with the RESULT SETS clause. Each cursor that is defined with the WITH RETURN clause that the procedure opens and leaves open when it returns identifies a result set.

If a SET RESULT SETS statement is executed in the procedure, the SET RESULT SETS statement identifies which of these result sets to return. The result sets are returned in the order specified on the SET RESULT SETS statement.
If a SET RESULT SETS statement is not executed in the procedure the result sets are returned in the order in which the cursors are opened.

When a result set is returned using an open cursor, the rows are returned starting with the current cursor position.

The RESULT SETS clause must be specified on the CREATE PROCEDURE (SQL) statement to return any result sets from an SQL procedure. The maximum number of result sets returned cannot be larger than the number specified on the CREATE PROCEDURE statement.

Example

The following SET RESULT SETS statement specifies cursor X as the result set that will be returned when the procedure is called. For more information and complete examples showing the use of result sets from ODBC clients, see the IBM® i Access Family topic collection.

    EXEC SQL SET RESULT SETS CURSOR X;