A stored procedure that returns a result set
An example stored procedure that displays a result set after it completes.
In this example, the USERA.RET1RS stored procedure has two output parameters. The first, defined as an integer, returns the SQL code. The second, defined as a character string, returns the SQL state. This stored procedure also returns a result set based on the following SELECT statement from the QMF supplied table Q.STAFF:
SELECT ID, NAME, DEPT, JOB, YEARS, SALARY, COMM FROM Q.STAFF
The DSQEC_SP_RS_NUM global variable indicates which result set is used to create the report. Verify that DSQEC_SP_RS_NUM is set to 1. Issue the CALL statement on the SQL QUERY panel:
CALL USERA.RET1RS(&A01, &A02)
Upon completion, you see this message:
OK, your stored procedure has successfully completed.
Your result set is displayed, as shown in the following figure; you can page forward or back to view the entire report.
You can view the output parameters by issuing the SHOW GLOBALS command. This command displays a list of all global variables and their values, including those global variables that are provided by QMF and those variables that are defined by users.