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

Which DB2 version and OS?

Andy

DB2 Version 9.2
Operating System Windows 2003


Regards
Vishal

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

CALL is a SQL statement on its own. It cannot be used as part of some other SQL statement like a SELECT statement.