Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2012
    Posts
    8

    Unanswered: How to trap Out Value in Dynamic Calling DB2 SP

    Hi folks,

    DB2 Version- v9.7.0.3
    OS-AIX64
    Tool- Toad

    I am facing a problem, how can we store the out parameter value in db2 SP while calling SP using prepare & execute statement;
    here is the sample SPs..

    CREATE PROCEDURE INNER_SP ( OUT "RETCODE" INTEGER )

    LANGUAGE SQL

    NOT DETERMINISTIC

    EXTERNAL ACTION

    CALLED ON NULL INPUT

    INHERIT SPECIAL REGISTERS

    BEGIN

    SET RETCODE=-1;

    END;





    CREATE PROCEDURE INNER_SP ()

    LANGUAGE SQL

    NOT DETERMINISTIC

    EXTERNAL ACTION

    CALLED ON NULL INPUT

    INHERIT SPECIAL REGISTERS

    BEGIN

    DECLARE SP_STR varchar(300);

    DECLARE RETCODE INTEGER;

    SET LOG_SCHEMA_NM='NGCOREBRD';



    /******CALL SP TO GET RETURN CODE RETCODE**************************/

    SET SP_STR = 'CALL '||LOG_SCHEMA_NM||'.INNER_SP(:RETCODE)';

    PREPARE CALLSTMT FROM SP_STR;

    EXECUTE CALLSTMT USING RETCODE;



    /********************VERIFY RETURN CODE AND TAKE ACTION ***************/

    IF RETCODE!=0

    THEN

    ROLLBACK;

    WRITE_LOG;

    ELSE

    COMMIT;

    END IF

    END;

    Thanks in Advance

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Code:
    prepare callstmt from 'call inner_sp(?)';
    execute callstmt into retcode;
    ---
    "It does not work" is not a valid problem statement.

Tags for this Thread

Posting Permissions

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