Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    18

    Question Unanswered: Returning a resultSet from Stored Procedure

    I have two sql stored procedures. Stored procedure 1 looks like this:

    P1 : BEGIN -- Declare cursor
    DECLARE CURSOR1 CURSOR WITH RETURN FOR
    SELECT AIPROD , AISTRQ
    FROM DAI
    WHERE DAI . AIWHS = 'S'
    ORDER BY DAI . AIPROD ;
    OPEN CURSOR1 ;
    END P1

    Stored procedure 2 is a stored procedure that will be used for many reasons it calculates a number returns it as an output based on the value AIPROD.

    I have java on my client side where I am calling the stored procedure and going through the result set from stored procedure 1 and then calling stored procedure 2 to get that piece of info.

    Can I have stored procedure 1 call stored procedure 2 and return all my data? If this is possible can someone please give me an example.

    Thanks in advance for any help.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Let me make sure that I understand what you desire.

    You want the result set from SP1 to include an additional column that is based on the calculation of SP2. Is this correct?

    Andy

  3. #3
    Join Date
    Nov 2003
    Posts
    18
    That is correct

  4. #4
    Join Date
    Nov 2003
    Posts
    18
    That is correct

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What I would do is use a temporary table, like this:

    P1 : BEGIN -- Declare cursor
    DECLARE GLOBAL TEMPORARY TABLE myTemp (AIPROD integer, AISTRQ integer, CAL_VALUE integer)
    WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED ;

    INSERT INTO session.myTemp (AIPROD,AISTRQ) SELECT AIPROD , AISTRQ
    FROM DAI
    WHERE DAI . AIWHS = 'S'

    BEGIN
    DECLARE num_rows integer;
    DECLARE cur_row integer;
    DECLARE value integer;
    DECLARE ret_value integer;

    DECLARE cursor1 CURSOR FOR SELECT AIPROD from session.myTemp;

    DECLARE cursor2 CURSOR WITH RETURN TO CALLER for select * from session.myTemp;

    SELECT count(*) into num_rows from session.myTemp;
    SET cur_row = 0;

    OPEN CURSOR1;
    WHILE cur_row < num_rows do
    FETCH cursor1 into value;
    CALL SP2(value,ret_value);
    UPDATE session.myTemp set cal_value = ret_value where current of cursor1;
    END WHILE;

    OPEN CURSOR2;
    END;



    END P1


    HTH

    Andy

  6. #6
    Join Date
    Nov 2003
    Posts
    18
    Thanks for the help Andy and getting back to me so quickly! I am hoping you can help me out on one more issue how do I make the cursor scrollable? For example when display the data to the user I want to show them the first 100 rows of data, if they click a link or button for next I want to show them the next 100. Is that possible with db2 stored procs?

    Thanks again for all your help I really appreciate it!

    Ted

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Ted,
    Cursors declared in SQL with DECLARE CURSOR
    are not the same as the type of cursor you are talking about. DECLARE CURSOR is a construct for processing a result set in SQL (which is not scrollable). It is also used to have a SP return a result set. DB2 uses the other kind of cursors for result sets, and these (I believe) are scrollable. Your application will have to do the work of controlling the scrolling through the cursor of the result set. ANd that depends on the language it is written in (if scrollable cursors are supported).

    Andy

    Originally posted by tmacksam
    Thanks for the help Andy and getting back to me so quickly! I am hoping you can help me out on one more issue how do I make the cursor scrollable? For example when display the data to the user I want to show them the first 100 rows of data, if they click a link or button for next I want to show them the next 100. Is that possible with db2 stored procs?

    Thanks again for all your help I really appreciate it!

    Ted

Posting Permissions

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