Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Posts
    78

    Unhappy Unanswered: Nested stored procedure problem, pls help!!!

    Hi guys,

    I am totally depressed. I found proof that DB2 support upto 16 levels nested stored procedure calls. And I also found an example of showing how to specify the statement in the caller stored procedure. Ok, to be short. Here is my statement in the caller SP:

    ....
    declare intTran bigint default 0;
    CALL ReqTranNum(intTran);
    ....

    the called SP runs fine, and has only one out parameter:
    CREATE PROCEDURE ReqTranNum (
    out iTran bigint
    )
    ....

    Please anyone tell me what's wrong? I'd appreciate.

  2. #2
    Join Date
    Jul 2003
    Posts
    78

    Re: Nested stored procedure problem, pls help!!!

    Sorry, forgot to tell you that the caller SP get null value from the called SP. But when I run the called SP, it works just as exactly as what I expected.

    Originally posted by christine2003
    Hi guys,

    I am totally depressed. I found proof that DB2 support upto 16 levels nested stored procedure calls. And I also found an example of showing how to specify the statement in the caller stored procedure. Ok, to be short. Here is my statement in the caller SP:

    ....
    declare intTran bigint default 0;
    CALL ReqTranNum(intTran);
    ....

    the called SP runs fine, and has only one out parameter:
    CREATE PROCEDURE ReqTranNum (
    out iTran bigint
    )
    ....

    Please anyone tell me what's wrong? I'd appreciate.

  3. #3
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    You probably need to give us a little more information if we are to know exacty what your problem is.

    You can pass parameters quite easily between SPs. You'll need to have them declared in your calling SP though if you want to use them (and they need to be out/inout parameters in your target SP)

    CREATE PROCEDURE SP_TEST_CALL (IN P_IN_CALLER INT, OUT P_OUT_CALLER INT)
    LANGUAGE SQL
    BEGIN
    DECLARE P_OUT_TARGET INT;
    CALL SP_TEST_TARGET(P_IN_CALLER, P_OUT_TARGET);
    SET P_OUT_CALLER = P_OUT_TARGET;
    END
    @
    CREATE PROCEDURE SP_TEST_TARGET (IN P_IN_TARGET INT, OUT P_OUT_TARGET INT)
    LANGUAGE SQL
    BEGIN
    SET P_OUT_TARGET = P_IN_TARGET * 10;
    END
    @

    Working with results sets (i.e. cursors) is a bit more tricky.

    The following thread contains an example of this ...

    http://dbforums.com/showthread.php?threadid=506070

    HTH

  4. #4
    Join Date
    Jul 2003
    Posts
    78

    Red face what if I don't have in parameter in target SP?

    My called SP does not have in parameter, only out parameter, is that the problem?

    Thanks a lot.

    Originally posted by Damian Ibbotson
    You probably need to give us a little more information if we are to know exacty what your problem is.

    You can pass parameters quite easily between SPs. You'll need to have them declared in your calling SP though if you want to use them (and they need to be out/inout parameters in your target SP)

    CREATE PROCEDURE SP_TEST_CALL (IN P_IN_CALLER INT, OUT P_OUT_CALLER INT)
    LANGUAGE SQL
    BEGIN
    DECLARE P_OUT_TARGET INT;
    CALL SP_TEST_TARGET(P_IN_CALLER, P_OUT_TARGET);
    SET P_OUT_CALLER = P_OUT_TARGET;
    END
    @
    CREATE PROCEDURE SP_TEST_TARGET (IN P_IN_TARGET INT, OUT P_OUT_TARGET INT)
    LANGUAGE SQL
    BEGIN
    SET P_OUT_TARGET = P_IN_TARGET * 10;
    END
    @

    Working with results sets (i.e. cursors) is a bit more tricky.

    The following thread contains an example of this ...

    http://dbforums.com/showthread.php?threadid=506070

    HTH

  5. #5
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Probably not.

    Show me your SPs!

  6. #6
    Join Date
    Jul 2003
    Posts
    78

    read on,

    The caller SP: (it is a little bit long, so I only show the related)

    create procedure CALLER_SP
    (
    in cmpny char(20),
    in divsn char(20),
    in empSSN char(10),
    in depSSN char(10),
    in covType char(20),
    in effDate char(8),
    in covName char(20),
    in usrID char(10),
    in covOpt char(20),
    in lname char(20),
    in entity char(20),
    in fname char(20),
    in mname char(1),
    out reqTranNum char(9)
    )

    LANGUAGE SQL
    modifies sql data
    BEGIN
    declare intTran bigint default 0;

    ...

    CALL ReqTranNum(intTran);

    set reqTranNum = char(intTran);
    ...
    end

    The called SP:

    CREATE PROCEDURE ReqTranNum (
    out iTran bigint
    )
    language sql
    modifies sql data
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    update requestTrn
    set rTranNum = (select (rTranNum+1) from requestTrn);
    set iTran = (select rTranNum from requestTrn);
    END P1


    Originally posted by Damian Ibbotson
    Probably not.

    Show me your SPs!

  7. #7
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Okay... There doesn't look to be anything wrong with that.

    It could be one of 2 things.

    1) REQUESTTRN has 0 rows
    2) You are nulling reqTranNum out elsewhere in CALLER_SP

    Let me know how you get on.

  8. #8
    Join Date
    Jul 2003
    Posts
    78

    Wink I am so sorry, it is another problem

    Sorry, it is my mistake, it is not the nested SP problem at all. I found that one of the column is nullable, and I forgot to put it in the insert statement, I thought it was that column, which I get value from the called SP. Sorry to waste your time.


    Originally posted by christine2003
    The caller SP: (it is a little bit long, so I only show the related)

    create procedure CALLER_SP
    (
    in cmpny char(20),
    in divsn char(20),
    in empSSN char(10),
    in depSSN char(10),
    in covType char(20),
    in effDate char(8),
    in covName char(20),
    in usrID char(10),
    in covOpt char(20),
    in lname char(20),
    in entity char(20),
    in fname char(20),
    in mname char(1),
    out reqTranNum char(9)
    )

    LANGUAGE SQL
    modifies sql data
    BEGIN
    declare intTran bigint default 0;

    ...

    CALL ReqTranNum(intTran);

    set reqTranNum = char(intTran);
    ...
    end

    The called SP:

    CREATE PROCEDURE ReqTranNum (
    out iTran bigint
    )
    language sql
    modifies sql data
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    update requestTrn
    set rTranNum = (select (rTranNum+1) from requestTrn);
    set iTran = (select rTranNum from requestTrn);
    END P1

Posting Permissions

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