Can a stored procedure return a result set?

In addition to returning output parameters, a stored procedure can return a result set (that is, a result table associated with a cursor opened in the stored procedure) to the application that issues the CALL statement. The application can then issue fetch requests to read the rows of the result set cursor.

How to return resultset from stored procedure in DB2?

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.

Can a stored procedure return multiple result sets?

Most stored procedures return multiple result sets. Such a stored procedure usually includes one or more select statements. The consumer needs to consider this inclusion to handle all the result sets.

How do I create a stored procedure in DB2?

Set up the workbench environment for stored procedure development. Connect to the GSDB sample database and create a data development project to work with the database. Create an SQL stored procedure. Deploy, debug, and run the stored procedure from the workbench.

How to process result sets in a DB2 procedure?

In DB2, a stored procedure can process result set (s) returned by another stored procedure using RESULT_SET_LOCATOR type, ASSOCIATE RESULT SET LOCATORS and ALLOCATE CURSOR FOR RESULT SET statements. In Oracle you can use SYS_REFCURSOR type to pass result sets between stored procedures.

When to use Result sets as cursors in DB2?

Section D –Process and close the cursors. Once the result sets have been “allocated” as cursors, they can be used like a normal cursor within the program. When ALLOCATE CURSOR is used, an explicit OPEN cursor statement is not required (because technically the stored procedure itself opens the cursor).

Can you call a DB2 stored procedure in SPUFI?

Stored Procedures are a powerful Db2 object that are being used by more and more of our applications. Famously, we all know that traditional “tools” like SPUFI or batch DSNTEP2 cannot call stored procedures. So inside TSO, it is hard to actually use and CALL stored procedures. Technically, we could build COBOL or REXX programs to call them.

When to return data from a stored procedure?

If multiple such SELECT statements are run during the execution of the stored procedure, multiple result sets will be sent to the client. This behavior also applies to nested TSQL batches, nested stored procedures and top-level TSQL batches.