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
SELECT V1 INTO V2 FROM DB.TBL WHERE PI = CC_MIN
CC_MIN = CC_MAX +1;
Thanks in advance!!!
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
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) ,
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;
SELECT col4 into var1 from vt2 where col8 = CC_MIN;
CALL db.sp2(''''||var1||'''',''|| tbl2||'', ''||tbl3||''
SET CC_MIN = CC_MIN + 1;