DB2 STORED PROCEDURE WITH IMBEDDED CALL FUNCTION

[cristi22]

please help!!!

We have various crystal intranet reports on the company's web-site and assosiated SPs.

Each time the user runs the CR report via the bill_test_call stored procedure:

i need to update the data in the ACCUMILATED_BILLING table based on the result set of the bill_test_call

here is what i've done so far...now how do i insert the whole RS?

CREATE PROCEDURE [b]BASYS.bill_test_call[/b] (  )
    DYNAMIC RESULT SETS 1
-----------------------------------------------------------
-- SQL Stored Procedure
----------------------------------------------------------
P1: BEGIN
    -- Declare cursor

    DECLARE stmt VARCHAR(4000);
      DECLARE loc1, loc2 RESULT_SET_LOCATOR VARYING;

     DECLARE cursor1 CURSOR WITH RETURN FOR s1;

     set stmt =
' SELECT '
|| '        c.association, cf.employer_cont_key,right(rtrim(CF.FUND),1) as code,''200607'' as WORK_PERIOD '
|| '          FROM         BASYS.CONTRACTS_FUND_DIS CF,       BASYS.CONTRACTORS C LEFT OUTER JOIN'
|| '        BASYS.EMPLOYER_STATUS_CODE S '
|| '        ON C.CONTRACTORS_KEY = S.EMPLOYER_STATUS_KEY '
|| '         WHERE CF.EMPLOYER_CONT_KEY = C.CONTRACTORS_KEY  '
|| '          and   (S.MVPOS IS NULL OR (S.MVPOS = 1 AND S.CODE NOT IN (''OOB'',''CLD''))) '
||'        and employer_cont_key=''05900206''  WITH UR' ;
    -- Cursor left open for client application
                         call BASYS.TEST_CALL_2  (stmt);

PREPARE s1 FROM stmt;
    -- Cursor left open for client application
    OPEN cursor1 ;
END P1

CREATE PROCEDURE BASYS.TEST_CALL_2(inout stm varchar (4000) )
-----------------------------------------------------------
-- SQL Stored Procedure
---------------------------------------------------------
P1: BEGIN
    -- Declare variable

     DECLARE loc1, loc2 RESULT_SET_LOCATOR VARYING;

    -- Declare handler

  DELETE FROM ACCUMILATED_BILLING;
    insert into   ACCUMILATED_BILLING VALUES (left(stm,2),'123456789','D','200701');

END P1