Db2 stored procedure return multiple rows

raqueliii · #1 (**permalink**) 02-12-10, 18:55

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.

n_i · #2 (**permalink**) 02-13-10, 08:11

There are no other results, because you close both cursors before returning from the SP.

raqueliii · #3 (**permalink**) 02-13-10, 09:13

Thxs! i comment those lines, but right now i only receive one of the results in columns, how can i concatenate the other column?

tonkuma · #4 (**permalink**) 02-13-10, 10:32

You got one result, because you executed only once "FETCH FROM cursor1 INTO ANIOI;" and "FETCH cursor2 INTO PROGACAD;".

I thought that the procedure SCHEMA.PROCED21 can be replaced by a view SCHEMA.V_ED21 or a table function SCHEMA.FUNCED21, which are like...

Code:

CREATE VIEW SCHEMA.V_ED21
( CVE_PROGRAMA , NOMBRE_CARRERA ) AS 
SELECT T3.ID_002 , T2.NOMBRE
  FROM SCHEMA.T_001 T2
  INNER JOIN
       SCHEMA.T_002 T5
   ON  T2.ID_001 = T5.ID_001
  INNER JOIN
       SCHEMA.T_118 T3
   ON  T5.ID_002 = T3.ID_002
  INNER JOIN
       SCHEMA.T_046 T1
   ON  T1.ID_046 = T3.ID_046
  INNER JOIN
       SCHEMA.T_122 T4
   ON  T1.ID_122 = T4.ID_122
   AND INICIO    = YEAR(CURRENT DATE)
   AND FIN       = YEAR(CURRENT DATE) + 1
;

or

Code:

CREATE FUNCTION SCHEMA.FUNCED21
 RETURNS TABLE( CVE_PROGRAMA   INTEGER
              , NOMBRE_CARRERA VARCHAR(120) )
 LANGUAGE SQL 
 DETERMINISTIC 
 NO EXTERNAL ACTION
 READS SQL DATA 
RETURN
SELECT T3.ID_002 , T2.NOMBRE
  FROM SCHEMA.T_001 T2
  JOIN SCHEMA.T_002 T5
   ON  T2.ID_001 = T5.ID_001
  JOIN SCHEMA.T_118 T3
   ON  T5.ID_002 = T3.ID_002
  JOIN SCHEMA.T_046 T1
   ON  T1.ID_046 = T3.ID_046
  JOIN SCHEMA.T_122 T4
   ON  T1.ID_122 = T4.ID_122
   AND INICIO    = YEAR(CURRENT DATE)
   AND FIN       = YEAR(CURRENT DATE) + 1
;

raqueliii · #5 (**permalink**) 02-13-10, 12:16

Thxs! But we only use stored procedures, if i want to return two columns how can i fetch it?

sathyaram_s · #6 (**permalink**) 02-13-10, 14:28

Quote:

Originally Posted by raqueliii

Thxs! But we only use stored procedures, if i want to return two columns how can i fetch it?

If you want to return a result set, then do not do any FETCH or CLOSE operations on it ..

Use the result set from the SP in the calling program ..

a) What platform/db2 version are you running on ?

b) How do you call the SP(say, JDBC, .Net etc)

tonkuma · #7 (**permalink**) 02-13-10, 16:11

Quote:

we only use stored procedures

Why?
I can't imagine rational reason to use such principal.
Would you like to explain the reason?

Anyway, if you want to create a procedure,
declare a cursor for my select statement and open it, end.
No other statement would be necessary, except the ones required to work the cursor.

tonkuma · #8 (**permalink**) 02-13-10, 19:52

To get multiple rows from a procedure, please see page 45 of the attached file.

The presentation in the file was DB2 8.1 level.
So, some informations were obsolete.

raqueliii · #9 (**permalink**) 02-14-10, 02:03

Hi! another time! Thxs for all the replies, the version is DB2 8.1 i dont know what is the reason to only use SP, orders are orders =P and we will call the SP with .NET.
Thxs for all!

Cougar8000 · #10 (**permalink**) 02-15-10, 09:57

It is great to have people who will agree to do anything

dav1mo · #11 (**permalink**) 02-15-10, 16:47

hey, its always existed. Years ago v5 on the mainframe it was do not use procedures. Same with a lot of new functionality/myths.
Dave

raqueliii · #12 (**permalink**) 02-18-10, 17:17

I believe that this is an open forum where u can write ur questions and the people who knows anwer that, if Cougar or Dav1mo are masters in DB2 ok, is fine, but please be a little more friendly towards the people who are asking. Thxs for tonkuma and the moderator.

n_i · #13 (**permalink**) 02-18-10, 18:29

Quote:

Originally Posted by raqueliii

I believe that this is an open forum

Exactly. People are free to voice their opinions, even if you don't agree with them. There's always an "off" button if you don't like something on the Internet.

DBFinder · #14 (**permalink**) 02-18-10, 22:29

Quote:

Originally Posted by raqueliii

but please be a little more friendly towards the people who are asking.

I agree, also when you work in a company, you have to regard others, especially big bosses.

For example, I normally can offer diffrent scripts for a job, they will be happier if I give them an SP.

DBFinder

stolze · #15 (**permalink**) 02-19-10, 17:12

Quote:

Originally Posted by DBFinder

..., especially big bosses.

Where do you draw that conclusion from? I learned that you have to stick to your opinion if you have good arguments supporting it - even your your boss has a different view (and no good arguments). Give it a try - it may not be the easier approach in the short run, though.

Quote:

For example, I normally can offer diffrent scripts for a job, they will be happier if I give them an SP.

That's not for the "big bosses", is it?