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.

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

That is correct

That is correct

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

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

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