Use Return Value of a Procedure in another Procedure

Database
Enthusiast

Use Return Value of a Procedure in another Procedure

Hi - I am working on converting TSQL Procedues into TD and need to call a SP [say SP2] from SP1 and in SP1 use the results obtained from SP2. Tried couple of things and lastly this as below:

REPLACE PROCEDURE UMS_DRAM.SP2(ID INTEGER)

DYNAMIC RESULT SETS 1

MAIN:BEGIN -- Main BEGINS Here

 DECLARE ProcName VARCHAR(100) DEFAULT 'SP2';

 DECLARE Cur1 CURSOR WITH RETURN ONLY TO CLIENT FOR

 SELECT :ID + 10 AS A;

 OPEN cur1;

END MAIN; 

REPLACE PROCEDURE UMS_DRAM.SP1(IN ID INTEGER)

MAIN:BEGIN -- Main BEGINS Here

DECLARE ProcRes, CurrCount INTEGER;

DECLARE SQLSTR VARCHAR(100);

DECLARE C1 CURSOR FOR S1;

 SET SQLSTR = 'CALL SP_VP_TEST(' || ID || ')';

 PREPARE S1 FROM SQLSTR;

 OPEN C1;

  FETCH C1 INTO CurrCount;

 CLOSE C1;

 INSERT INTO VP_TEST(CurrCount);

END MAIN;

Didnt work...Basically my requirement is to call one Procedure [by passing some value to it] inside another Procedure, Get the results returned and use it in the Calling Procedure...Use means it could be as simple as insert into a table.

Can anyone please share your thoughts?

Thanks,

Vinay

1 REPLY
Enthusiast

Re: Use Return Value of a Procedure in another Procedure

Hi - I found a solution for this, thought it would help someone:

CREATE  PROCEDURE SP2(ID INTEGER)

 DYNAMIC RESULT SETS 1

 MAIN:BEGIN -- Main BEGINS Here

  DECLARE ProcName VARCHAR(100) DEFAULT 'SP2';

  DECLARE Cur1 CURSOR WITH RETURN ONLY FOR

  SELECT :ID + 10 AS A;

  OPEN cur1;

 END MAIN;

REPLACE PROCEDURE SP1(IN ID INTEGER, OUT OP INTEGER)

 MAIN:BEGIN -- Main BEGINS Here

 DECLARE ProcRes, CurrCount INTEGER;

 DECLARE SQLSTR VARCHAR(100);

 DECLARE C1 CURSOR FOR S1;

 CALL TestSP2(ID);

 ALLOCATE my_fetch CURSOR FOR PROCEDURE SP2;

 FETCH FROM my_fetch INTO CurrCount;

 CLOSE my_fetch;

 SET OP=CurrCount;

END MAIN;

CALL SP1(100,RETCODE)