Programming stored procedures to return result sets

You can perform the following actions:

• For each result set the stored procedure returns, declare a cursor with the WITH RETURN option, open the cursor on the result set (that is, execute a query), and leave the cursor open after you exit the procedure.
• Return a result set for every cursor that is left open after exit, in the order in which the procedure opened the corresponding cursors.
• Pass only unread rows back to the Db2 ODBC client application.

  For example, if the result set of a cursor has 500 rows, but the stored procedure reads 150 of those rows before it terminates, the stored procedure returns only rows 151 through 500. You can use this behavior to filter out initial rows in the result set before you return them to the client application.

More specifically, to write a Db2 ODBC stored procedure that returns result sets, you must include the following procedure in your application:

1. Issue SQLExecute() or SQLExecDirect() to perform a query that opens a cursor. In stored procedures, Db2 ODBC declares cursors with the WITH RETURN option.
2. Optionally, issue SQLFetch() to read rows that you want to filter from the result set.
3. Issue SQLDisconnect(), SQLFreeHandle() with HandleType set to SQL_HANDLE_DBC, and SQLFreeHandle() with HandleType set to SQL_HANDLE_ENV to exit the stored procedure. This exit leaves the statement handle, and the corresponding cursor, in a valid state.

Do not issue SQLFreeHandle() with HandleType set to SQL_HANDLE_STMT or SQLCloseCursor(). When you do not free the statement handle or explicitly close the cursor on that handle, the cursor remains open to return result sets. If you close a cursor before the stored procedure exit, it is a local cursor. If you keep a cursor open after you exit the stored procedure, it returns a query result set (also called a multiple result set) to the client application.