Getting an output variable from a stored proc in a stored proc

[Todd Bryant <tbryant@xxxxxxxxxxxxxxxxxxxxxxx>]

Hi, I have a stored procedure question and though it is not technically
Java, it is going to be used in a Websphere environment so I thought someone
might be able to help me. 

We have a stored procedure that we are trying to get an output variable to
work. I know how to register an output variable when calling a stored proc
that accepts one in Java (though I have not actually done it. I just know
how), but we want to call a stored proc from within an SQL stored proc and
get the output variable. Maybe looking at our code will help (See code
below. we are trying to get a "spendable amount" from another stored proc.
The other stored proc is written in RPG and appears to be working properly.
The called stored proc takes a char string input var and accepts a char
output var. It returns no resultset). 

Maybe you can only get output variables in any other language than SQL
procedure language? Or maybe we are just missing something simple? I truly
appreciate any help anyone can give. 

-----------------
CREATE PROCEDURE TST.FUND_DETAILS_1 ( 
        IN FUND CHAR(8) , 
        IN LIBRARY CHAR(15) ) 
        DYNAMIC RESULT SETS 1 
        LANGUAGE SQL 
        SPECIFIC TST.FUND_DETAILS_1 
        NOT DETERMINISTIC 
        MODIFIES SQL DATA 
        CALLED ON NULL INPUT
BEGIN 
DECLARE STMT CHAR(4000);
DECLARE SPENDABLE CHAR(20);
DECLARE C1 CURSOR WITH RETURN FOR S1;


/* set spendable amount here */
CALL TST.ACTQ010('30013550', SPENDABLE);

SET STMT = 'SELECT FUND.ACTNO AS ACCOUNT_NUMBER,
        FUND.ACTNM AS SHORT_NAME,
        NAME.RCFDCP AS LONG_NAME,
        END.CODDSC AS ENDOWMENT,
/* concatenate amount in here */
        ''' || SPENDABLE || ''' AS SPENDABLE_AMT,
        PUR.CODE30 AS PURPOSE,
        DEV.TCNAM AS DEV_OFFICER,
        ADDR.PHONE AS PHONE_NUMBER,
        SUM(INVEST.INBKVL) AS BOOK_VALUE,
        SUM(INVEST.INMKVL) AS MARKET_VALUE,
        FUND31.CASH AS PRINCIPAL_CASH,
        FUND.PLEDGE AS PLEDGE,
        FUND.INVEST AS INVESTED,
        FUND.CASH AS INCOME_CASH,
        FUND.TOTBAL AS TOTAL_BALANCE,
        HIER1.TBODSC AS CAMPUS_DESC,
        HIER2.TBODSC AS INST_DESC, 
        HIER3.TBODSC AS COLL_DESC,
        HIER4.TBODSC AS DEPT_DESC,
        HIER5.TBODSC AS SUB_DESC
FROM
        ' || LIBRARY ||'.LDSP010 AS FUND
        inner join ' || LIBRARY ||'.LDSL014 AS FUND31
                on (SUBSTR(FUND.ACTNO, 3, 5) = SUBSTR(FUND31.ACTNO, 3, 5))

        inner join ' || LIBRARY || '.RCTP080 AS NAME
                on (FUND.ACTNO = NAME.RCFNDC)
        inner join ' || LIBRARY || '.TABP120 AS END
                on (FUND.ENDWL = TRIM(END.CODE))
        inner join ' || LIBRARY || '.TABP300 AS PUR
                on (FUND.LDLD03 = PUR.CODE3)
        inner join ' || LIBRARY || '.TABP290 AS DEV
                on (FUND.CONT1 = DEV.TCONT)
        left join ' || LIBRARY || '.ADDP010 AS ADDR
                ON (DEV.TCMID# = ADDR.MSTID)
        left join ' || LIBRARY || '.INVP020 AS INVEST
                on (FUND.ACTNO = INVEST.INACT#)
        left join ' || LIBRARY || '.TABL476 AS HIER1
                on (FUND.LDOLV1 = HIER1.TBOLV1)
        left join ' || LIBRARY || '.TABL472 AS HIER2
                on (FUND.LDOLV1 = HIER2.TBOLV1
                AND FUND.LDOLV2 = HIER2.TBOLV2)
        left join ' || LIBRARY || '.TABL473 AS HIER3
                on (FUND.LDOLV1 = HIER3.TBOLV1
                AND FUND.LDOLV2 = HIER3.TBOLV2
                AND FUND.LDOLV3 = HIER3.TBOLV3)
        left join ' || LIBRARY || '.TABL474 AS HIER4
                on (FUND.LDOLV1 = HIER4.TBOLV1
                AND FUND.LDOLV2 = HIER4.TBOLV2
                AND FUND.LDOLV3 = HIER4.TBOLV3
                AND FUND.LDOLV4 = HIER4.TBOLV4)
        left join ' || LIBRARY || '.TABL475 AS HIER5
                on (FUND.LDOLV1 = HIER5.TBOLV1
                AND FUND.LDOLV2 = HIER5.TBOLV2
                AND FUND.LDOLV3 = HIER5.TBOLV3
                AND FUND.LDOLV4 = HIER5.TBOLV4
                AND FUND.LDOLV5 = HIER5.TBOLV5)

WHERE FUND.ACTNO = ?
        AND END.FIELD = ''ENDWL''
        AND PUR.FIELD = ''UDLD03'' 
        AND ADDR.ADDTYP = ''B''

GROUP BY
        FUND.ACTNO, FUND.ACTNM, NAME.RCFDCP, END.CODDSC, PUR.CODE30,
DEV.TCNAM, 
        ADDR.PHONE, FUND31.CASH, FUND.PLEDGE, FUND.INVEST, FUND.CASH,
FUND.TOTBAL,HIER1.TBODSC,
        HIER2.TBODSC, HIER3.TBODSC, HIER4.TBODSC, HIER5.TBODSC';

PREPARE S1 FROM STMT;
OPEN C1 USING FUND;
END
------------------------------------


Todd Bryant, SCJP
Programmer/Analyst
University of Nebraska Foundation
402-472-0107
?