Results 1 to 5 of 5
  1. #1
    Join Date
    May 2009
    Posts
    12

    Red face Unanswered: How to call one procedure from another procedure in a select block.

    Hi all,
    How to call one procedure from another procedure in a select block?
    Here is the code for reference

    CREATE PROCEDURE PROC1 (IN i_perinc VARCHAR(2),OUT result float)
    LANGUAGE SQL
    Begin
    declare cost float;
    DECLARE c1 CURSOR WITH RETURN FOR
    SELECT product_id,item_cost cost1 FROM product where product_id = i_perinc;
    SET cost = cost1;
    OPEN c1;
    END


    Now I am trying to call this SP from another SP given below:

    CREATE PROCEDURE PROC2 ( IN Name CHAR(4) )
    LANGUAGE SQL
    DYNAMIC RESULT SETS 1
    P1: BEGIN
    -- Declare cursor
    Declare v1 VARCHAR(2);
    DECLARE cursor1 CURSOR FOR
    SELECT
    AIRPORT_CODE,
    call PROC1(AIRPORT.i_perinc)
    FROM AIRPORT AS AIRPORT WHERE AIRPORT.AIRPORT_CODE = Name;
    OPEN cursor1;
    fetch cursor1 into v1;
    END P1


    Regards
    Vishal

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Which DB2 version and OS?

    Andy

  3. #3
    Join Date
    May 2009
    Posts
    12
    DB2 Version 9.2
    Operating System Windows 2003


    Regards
    Vishal

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You need to look at the ASSOCIATE LOCATORS and the ALLOCATE CURSOR statements.

    IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

    IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

    Andy

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    CALL is a SQL statement on its own. It cannot be used as part of some other SQL statement like a SELECT statement.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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