Stored procedure USERA.RET3RS has two output parameters. The first, defined as an integer, returns the SQLCODE. The second, defined as a character string, returns the SQLSTATE. This stored procedure will also return three result sets based on the following SELECT statements from the QMF supplied tables Q.STAFF, Q.ORG, and Q.INTERVIEW:
SELECT NAME, DEPT, SALARY, COMM FROM Q.STAFF SELECT DEPTNUMB, DEPTNAME, MANAGER, DIVISION LOCATION FROM Q.ORG SELECT TEMPIDL, INTDATE, STARTTIME, ENDTIME, MANAGER, DISP, LASTNAME, FIRSTNAM FROM Q.INTERVIEW
You can display a result set each time that you run the stored procedure. Indicate the result set to be displayed by setting the QMF global variable DSQEC_SP_RS_NUM. Setting the value to 1 will display the first result set returned, setting to 2 will display the second result set returned, and so on. In the following example, DSQEC_SP_RS_NUM is set to 3 to display the third result set returned:
SET GLOBAL (DSQEC_SP_RS_NUM=3
Issue the CALL statement on the SQL QUERY panel:
CALL USERA.RET1RS(&A01, &A02)
Upon completion you will see this message:
OK, Your Stored Procedure has successfully completed.
Figure 183 shows an example of a displayed result set:
REPORT LINE 1 POS 1 79 TEMPID INTDATE STARTTIME ENDTIME MANAGER DISP LASTNAME FIRSTNAM ------ ---------- --------- ------- ------- ---- --------- -------- 400 1996-02-05 13.00.00 15.12.00 270 NOHIRE FROMMHERZ RICHARD 410 1996-02-11 15.00.00 16.18.00 10 HIRE JACOBS SUSAN 420 1996-04-07 09.00.00 09.58.00 140 HIRE MOTNEZ RITA 430 1996-04-24 10.30.00 11.30.00 290 NOHIRE RICHOWSKI JOHN 440 1996-03-13 10.15.00 11.23.00 160 HIRE REID CATHY 450 1996-09-19 09.45.00 11.00.00 50 HIRE JEFFREYS PAUL 460 1996-10-06 14.45.00 16.22.00 100 HIRE STANLEY JOHN 470 1996-02-05 16.30.00 18.00.00 270 HIRE CASALS DAVID 480 1996-03-13 13.30.00 14.45.00 160 NOHIRE LEEDS DIANE 490 1996-09-30 15.00.00 15.44.00 140 NOHIRE GASPARD PIERRE *** END *** 1=Help 2= 3=End 4=Print 5=Chart 6=Query 7=Backward 8=Forward 0=Form 10=Left 11=Right 12= OK, this is the REPORT from your RUN command. COMMAND ===> SCROLL ===> PAGE
The output parameters can be viewed in the global variable pool by using the SHOW GLOBALS command.