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.

Figure 1. Result set from USERA.RET1RS

 REPORT                         LINE 1    POS 1     79
 

     ID  NAME      DEPT  JOB   YEARS    SALARY        COMM
   ----  --------- ----  ----- -----     --------  -------
     10  SANDERS     20  MGR       7    99999.99         -
     20  PERNAL      20  SALES     8    18171.25    612.45
     30  MARENGHI    38  MGR       5    17506.75         -
     40  O’BRIEN     38  SALES     6    18006.00    846.55
     50  HANES       15  MGR      10    20659.80         -
     60  QUIGLEY     38  SALES     -    16808.30    650.25
     70  ROTHMAN     15  SALES     7    16502.83   1152.00
     80  JAMES       20  CLERK     -    13504.60    128.20
     90  KOONITZ     42  SALES     6    18001.75   1386.70
    100  PLOTZ       42  MGR       7    18352.80         -
    110  NGAN        15  CLERK     5    12508.20    206.60
    120  NAUGHTON    38  CLERK     -    12954.75    180.00
    130  YAMAGUCHI   42  CLERK     6    10505.90     75.60
    140  FRAYE       51  MGR       6    21150.00         -

 1=Help    2=     3=End     4=Print     5=Chart    6=Query
 7=Backward   8=Forward   9=Form   10=Left   11=Right  12=  
 OK, this is the REPORT from your RUN command
 COMMAND ===>

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.