DB2 graphic  QMF Version 8

Running a stored procedure that returns multiple result sets

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:

Figure 183. The result set from USERA.RET3RS
 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.


Go to the previous page Go to the next page

Downloads | Library | Support | Support Policy | Terms of use | Feedback
Copyright IBM Corporation 1982,2004 Copyright IBM Corporation 1982, 2004
timestamp Last updated: March, 2004