Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: Leaving a Stored Procedure

    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!
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Nov 2003
    Location
    kualaumpur
    Posts
    33

    Talking

    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

  3. #3
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    You can use 'RETURN <return value>'

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

    Damian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •