Db2 stored procedure fetch multiple rows

[RAQUELIII]

HI!
I MADE A STORED PROCEDURE BUT WHEN I CALL IT ONLY RETURN THE FIRST RECORD AS A MESSAGE, AND DOESNT RETURN ALL THE RECORDS THAT I QUERY.
THIS IS THE CODE:
CREATE PROCEDURE SCHEMA.PROCED21( OUT CVE_PROGRAMA INTEGER, OUT NOMBRE_CARRERA VARCHAR(120) )

RESULT SETS 1

LANGUAGE SQL

NOT DETERMINISTIC

READS SQL DATA

P1: BEGIN

DECLARE ANIOI INTEGER;

DECLARE ANIOF INTEGER;

DECLARE PROGACAD INTEGER;

DECLARE CVE_PROGRAMA_TEMP INTEGER;

DECLARE NOMBRE_CARRERA_TEMP VARCHAR(120);

DECLARE CVE_CARRERA INTEGER;

DECLARE cursor1 CURSOR WITH RETURN TO CALLER FOR

SELECT YEAR (CURRENT TIMESTAMP) FROM sysibm.sysdummy1;

DECLARE cursor2 CURSOR WITH RETURN TO CALLER FOR

SELECT T3.ID_002 FROM SCHEMA.T_046 T1 INNER JOIN SCHEMA.T_118 T3 ON (T1.ID_046=T3.ID_046) INNER JOIN SCHEMA.T_122 T4 ON (T1.ID_122=T4.ID_122 AND INICIO=ANIOI AND FIN =ANIOF);

OPEN cursor1;

FETCH FROM cursor1 INTO ANIOI;

CLOSE cursor1;

SET ANIOF = ANIOI+1;

OPEN cursor2;

FETCH cursor2 INTO PROGACAD;

FOR I AS cursor3 CURSOR WITH HOLD FOR

SELECT T5.ID_001, T5.ID_002 FROM SCHEMA.T_002 T5 WHERE T5.ID_002 =PROGACAD

DO

SET CVE_CARRERA = ID_001;

SET CVE_PROGRAMA_TEMP = ID_002;

FOR J AS cursor4 CURSOR WITH HOLD FOR

SELECT NOMBRE FROM SCHEMA.T_001 WHERE ID_001=CVE_CARRERA

DO

SET NOMBRE_CARRERA_TEMP= NOMBRE;



END FOR;



END FOR;

CLOSE cursor2;

SET CVE_PROGRAMA = CVE_PROGRAMA_TEMP;

SET NOMBRE_CARRERA = NOMBRE_CARRERA_TEMP;

RETURN CVE_PROGRAMA;

END P1;

I WANT TO SEE THE RESULTS AND IN MESSAGE WINDOW ONLY REPORT:

> CALL SCHEMA.PROCED21(?,?)

Return Code = 85

Output Parameter #1 = 85
Output Parameter #2 = ADMINISTRACIÓN

Statement ran successfully (99 ms)

CAN U HELP ME PLEASE?
THXS.

[jp140768]

Hi,

If you are using DB2/400, this link may help (see chapter 5.5).
http://www.redbooks.ibm.com/redbooks...tml/wwhelp.htm

It suggests that once you have opened the cursor that you want returned, to the calling progam, you issue something similar to:

DECLARE c1 CURSOR FOR SELECT cusnam FROM customer ORDER BY cusnam;      
OPEN c1;      
SET RESULT SETS CURSOR c1; 
 

Hope this helps,

JP