Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2010
    Posts
    7

    Exclamation Unanswered: Db2 stored procedure return multiple rows

    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.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    There are no other results, because you close both cursors before returning from the SP.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Feb 2010
    Posts
    7
    Thxs! i comment those lines, but right now i only receive one of the results in columns, how can i concatenate the other column?

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
    ;
    Last edited by tonkuma; 02-13-10 at 11:58. Reason: Add an example of view.

  5. #5
    Join Date
    Feb 2010
    Posts
    7

    Red face

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

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by raqueliii View Post
    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)
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Attached Files Attached Files

  9. #9
    Join Date
    Feb 2010
    Posts
    7
    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!

  10. #10
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    It is great to have people who will agree to do anything
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  11. #11
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  12. #12
    Join Date
    Feb 2010
    Posts
    7
    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.

  13. #13
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by raqueliii View Post
    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.
    ---
    "It does not work" is not a valid problem statement.

  14. #14
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by raqueliii View Post
    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

  15. #15
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by DBFinder View Post
    ..., 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.

    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?
    Last edited by stolze; 02-19-10 at 18:24.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •