Internal stored procedure getting executed only once within a do-while loop in a main stored procedure.

Database
TD2
N/A

Internal stored procedure getting executed only once within a do-while loop in a main stored procedure.

I am trying to call a stored procedure within a do-while loop in a main stored procedure.

The problem is that internal stored procedure is getting executed only once and the code exits from the internal stored procedure rather than executing in a loop.

Could you please help mein identifying the reason for the above problem.

My code looks like this

WHILE(CC_MAX<=CC_MIN)

DO

SELECT V1 INTO V2 FROM DB.TBL WHERE PI = CC_MIN

CALL DB.SP(DB,TB,V1);

CC_MIN = CC_MAX +1;

END WHILE

Thanks in advance!!!

NIAR

2 REPLIES

Re: Internal stored procedure getting executed only once within a do-while loop in a main stored procedure.

Niar,

Please post the rest of the outer SP.  Without seeing how you are setting cc_max & cc_min, it's tough to evaluate what might be happening

Thanks

TD2
N/A

Re: Internal stored procedure getting executed only once within a do-while loop in a main stored procedure.

Hi VandeBergB

The outer sp is provided below. Intially i am creating a table by picking up columns and datatypes from other table.

Later, I am calling a stored procedure in the second do-while loop, which is supposed iterate for every var1 value.

However, in my case the sp2 is executing as per the intial var1 value after which it exits through the sp2 instead of

coming back into the main procedure.

REPLACE PROCEDURE db.proc(IN ip1 VARCHAR(24), IN ip2 INTEGER)

MAIN:BEGIN -- Main BEGINS Here

 DECLARE var3,var1,var2,var4,var5 VARCHAR(24);

 DECLARE qt2,qr3,qc,qry_ins VARCHAR(8000);

 DECLARE CC_MAX,CC_MIN  INTEGER;

 SET var3 = ip1;

CREATE VOLATILE TABLE vt2

     (

      col4 VARCHAR(24) ,

      col5 VARCHAR(6) ,

      col6 INTEGER,

      col7 INTEGER,

   col8 INT )

PRIMARY INDEX ( col4) on commit preserve rows;

SET QRY_INS =    'INSERT INTO vt2 SEL col4,col5,col6,col7,ROW_NUMBER() OVER (ORDER BY col7) AS col8

        FROM st2 WHERE scol1 = '||''''|| ip1 ||''''|| ';';

EXECUTE IMMEDIATE QRY_INS;

SEL MAX(col8),MIN(col8) INTO CC_MAX,CC_MIN FROM vt2;

WHILE (CC_MIN<=CC_MAX)

  DO

   SELECT col4 into var1 from vt2 where  col8 = CC_MIN;

   CALL db.sp2(''''||var1||'''',''|| tbl2||'', ''||tbl3||''

           ,''||var3||'',''||var4||'',var5); 

   SET CC_MIN = CC_MIN + 1;

  END WHILE;  

END MAIN;