Is there any way to exit out of a stored procedure? Checked out EXIT and LEAVE but doesn't seem to be doing the trick:

Here's the proc:

DECLARE dateExpiretime TIMESTAMP;
DECLARE dateStartTime TIMESTAMP;
DECLARE ERROR_CURSOR1 CURSOR WITH RETURN FOR SELECT 'NOT A VALID CONNECTION' FROM SYSIBM.SYSDUMMY1;
DECLARE ERROR_CURSOR2 CURSOR WITH RETURN FOR SELECT 'CONNECTION NO LONGER VALID. CONNECTION CLOSED' FROM SYSIBM.SYSDUMMY1;
DECLARE CURSOR1 CURSOR WITH RETURN FOR SELECT 'VALID CONNECTION' FROM SYSIBM.SYSDUMMY1;
IF decAIMConnectionID NOT IN (SELECT AIMConnectionID FROM AIM.AIMCONNECTION) THEN
OPEN ERROR_CURSOR1;
END IF;
SET dateStartTime = (SELECT StartDateTime FROM AIM.AIMCONNECTION WHERE AIMCONNECTIONID = decAIMConnectionID);
SET dateExpireTime = dateStartTime + 8 HOURS;
IF CURRENT TIMESTAMP > dateExpireTime THEN
UPDATE AIM.AIMCONNECTION SET ENDDATETIME = CURRENT TIMESTAMP,
TerminationReason = 'Expired' WHERE AIMConnectionID = decAIMConnectionID;
OPEN ERROR_CURSOR2;
ELSE
OPEN CURSOR1;
END IF;
END P1

After the proc opens CURSOR1 (if it has to), I want to jump to the end of the procedure and cease processing.

Any advice?

Thanks!

Hi
u need to use

DECLARE c_exit CONDITION FOR SQLSTATE '99001';

DECLARE EXIT HANDLER FOR SQL EXCEPTION
BEGIN
SET v1 = 'YOUR ERROR MSG';
END ;

DECLARE EXIT HANDLER FOR c_exit
BEGIN
SET v1 = 'YOUR ERROR MSG';
END ;

[inside body u need to do ...]

IF {your exit condition succeeds } THEN
SIGNAL c_exit ;
END IF;




hopefully this will work ......

regards

Trinmoy

You can use 'RETURN <return value>'

Or maybe 'GOTO <a label at the end of your SP>'

Damian