Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2012
    Posts
    24

    Unanswered: Problems when procedure has two OUT variables

    Hi,

    I have a stored procedure calling another stored procedure. When I have implemented the stored procedure to return only one OUT value, it works fine. However, when I set it to return two OUT values, I get sqlcode -440 -error. The approximate code is below:

    CREATE OR REPLACE PROCEDURE SP_PROCEDURE (IN V_IN1 VARCHAR(128), IN V_IN2 INT, OUT V_OUT1 INT, OUT V_OUT2 INT)
    LANGUAGE SQL
    BEGIN
    IF(V_IN1 = ...)
    THEN
    SET (V_OUT1, V_OUT2) = (
    SELECT TABLE1.COL1, TABLE2.COL2
    FROM ... )
    END IF;

    Do the output variables need to be handled differently when there are more than one?

    The procedure is simply called with the below statement:

    DECLARE V_IN1 VARCHAR(128);
    DECLARE V_IN2 INTEGER;
    DECLARE V_OUT1 INTEGER;
    DECLARE V_OUT2 INTEGER;


    CALL SP_PROCEDURE(V_IN1, V_IN2, V_OUT1, V_OUT2);

    Like I stated earlier, this worked when V_OUT2 is not part of the code above at all.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What DB2 version/release/fixpack and platform OS?

  3. #3
    Join Date
    Aug 2012
    Posts
    24
    Quote Originally Posted by tonkuma View Post
    What DB2 version/release/fixpack and platform OS?
    Windows 7, DB2 9.7 fixpack 4

  4. #4
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    I'm able to compile an SQL PL procedure that has 2 output parameters (i.e. I do not get any -440 error). Also on Windows 7 , DB2 9.7 but fixpack 5.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by SoftwareEngineer3 View Post
    The approximate code is below:
    I can approximately tell you that you either have defined your procedure incorrectly or are calling it incorrectly.

    For more details read the error description and suggestions in the Messages manual.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by SoftwareEngineer3 View Post
    SET (V_OUT1, V_OUT2) = (
    SELECT TABLE1.COL1, TABLE2.COL2
    FROM ... )
    Maybe try replacing this by
    Code:
    SELECT TABLE1.COL1, TABLE2.COL2
    INTO V_OUT1, V_OUT2
    FROM ...
    (I've never seen the parenthesized form of "SET" being used.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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