Stored Procedure Behaving Differently when run multiple times

Database

Stored Procedure Behaving Differently when run multiple times

Hi - I have two stored procedures, P1 which doesnt return returnCode as OUT and P2 with returncode as OUT paramter. P1 works as-expected on multiple runs b ut P2 behaves differently when run multiple times...below is the content of P2.

REPLACE PROCEDURE P2(OUT returncode SMALLINT)

MAIN:BEGIN

DECLARE iRC SMALLINT;

DECLARE recCount INTEGER;

DECLARE sSQLErrorState CHAR(5) DEFAULT '00000';

DECLARE CONTINUE HANDLER

FOR SQLEXCEPTION

BEGIN

SET sSQLErrorState = SQLSTATE;

END;

SP_LABEL:

BEGIN

DELETE TBL1;

INSERT INTO TBL1 SELECT TOP 99 * FROM VIEW1 WHERE Create_time > ( SELECT MAX(create_time) FROM TBL2);

SET recCount=ACTIVITY_COUNT;

INSERT INTO TEMP_TBL(CURRENT_TIMESTAMP,'xyz',reccount,ACTIVITY_COUNT);

IF (reccount > 0) THEN -- BEGIN of Activity_Count > 0

BEGIN

INSERT INTO TBL2 SELECT MAX(CREATE_TS), COUNT(*) FROM TBL1;

SET iRC=0;

END;

END IF; -- END of Activity_Count > 0

IF (reccount = 0) THEN -- BEGIN of Activity_Count = 0

BEGIN

SET iRC=1;

LEAVE SP_LABEL;

END;

END IF; -- END of Activity_Count = 0

--END TRANSACTION;

END; -- Sub BEGIN -- ENDS HERE

SET returnCode=iRC;

INSERT INTO TEMP_TBL(CURRENT_TIMESTAMP,'ABC',iRC,iRC);

END MAIN;

At times, the first INSERT after DELETE works as-expected, meaning gives no. of inserted records for ACTIVITY_COUNT and then all is well....the other time the ACTIVITY_COUNT for this INSERT is 0 not sure why. if i take the same INSERT statement and run individually it works well.

Also, the other procedure P1 is as below which works well for all executions.

REPLACE PROCEDURE P1()

MAIN:BEGIN -- Main BEGIN -- BEGINS Here

DECLARE iRC SMALLINT;

DECLARE recCount INTEGER;

--DECLARE sSQLErrorState CHAR(5) DEFAULT '00000';

/*DECLARE CONTINUE HANDLER

FOR SQLEXCEPTION

BEGIN

SET sSQLErrorState = SQLSTATE;

END;*/

SP_LABEL:

BEGIN -- Sub Begin -- BEGINS Here

DELETE TBL1;

INSERT INTO TBL2 SELECT TOP 99 * FROM VIEW1 WHERE Create_time > ( SELECT MAX(create_time) FROM TBL2);

SET recCount=ACTIVITY_COUNT;

INSERT INTO TEMP_TBL(CURRENT_TIMESTAMP,'xyz',reccount,reccount);

 IF (reccount > 0) THEN -- BEGIN of Activity_Count > 0

BEGIN

INSERT INTO TBL1 SELECT MAX(CREATE_TS), COUNT(*) FROM TBL2;

SET iRC=0;

END;

END IF; -- END of Activity_Count > 0

 

IF (reccount = 0) THEN -- BEGIN of Activity_Count = 0

BEGIN

SET iRC=1;

LEAVE SP_LABEL;

END;

END IF; -- END of Activity_Count = 0

 

END; -- Sub BEGIN -- ENDS HERE

--SET returnCode=iRC;

INSERT INTO TEMP_TBL(CURRENT_TIMESTAMP,'ABC',iRC,iRC);

END MAIN;

 Please help with your thoughts.

Tags (1)