Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2007
    Posts
    56

    Unanswered: Stored procedure - Error handler(s)

    I am trying to write a stored procedure but am having difficulty with error handlers. If the first select statement returns a sqlstate = 02000, I want the stored procedure to terminate at that point.

    I'm using examples from DB2 for os/390 & z/os. When testing, it does not matter whether or not the case_id is valid, the sqlstate returned = 02000.

    If anyone can point out what I'm doing wrong I sure would appreciate it.

    Thank you.


    CREATE PROCEDURE SERDB.TESTSPDD
    (IN CASE_ID INT,
    IN TYPE_UPD CHAR(2),
    IN NEWUSER CHAR(13),
    OUT FAILEDAT CHAR(6),
    OUT NOTFOUND CHAR(1),
    OUT SRVCOORLEV SMALLINT,
    OUT POSITION_TYPE CHAR(1),
    OUT SQLSTATE_OUT CHARACTER(5),
    OUT SQLCODE_OUT INTEGER)
    LANGUAGE SQL
    MODIFIES SQL DATA
    COLLID SER
    COMMIT ON RETURN NO
    WLM ENVIRONMENT DDSNSPENV
    RUN OPTIONS 'NOTEST(ALL,*,,VADTCPIP&10.81.128.205:*)'

    P1: BEGIN

    --Declare variables
    DECLARE not_found CONDITION FOR '02000';
    DECLARE NOTFOUNDIND CHAR(1) DEFAULT 'N';
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
    DECLARE SQLCODE INT DEFAULT 0;
    DECLARE BEG_DT DATE;
    DECLARE BEG_DT1 DATE;
    DECLARE BEG_DT2 DATE;
    DECLARE PLAN_DUE_DT DATE;
    DECLARE ELIG_STAT CHAR(1);
    DECLARE POS_TYPE CHAR(1);
    DECLARE POS_TYPE1 CHAR(1);
    DECLARE POS_TYPE2 CHAR(1);
    DECLARE SRV_COOR_LEV SMALLINT;
    DECLARE DETER_DT DATE;
    DECLARE SRVCOOR_BEG_DT DATE;
    DECLARE SIN_PLN_DT DATE;
    DECLARE PLAN_DT DATE;

    --Declare cursor
    DECLARE cursor1 CURSOR WITH RETURN FOR
    SELECT SCHEMA, NAME FROM SYSIBM.SYSROUTINES;

    --Declare handler
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    SELECT SQLSTATE, SQLCODE INTO SQLSTATE_OUT, SQLCODE_OUT
    FROM SYSIBM.SYSDUMMY1;

    -- DECLARE CONTINUE HANDLER FOR
    -- not_found SET NOTFOUNDIND = 'Y';

    DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET NOTFOUNDIND = 'Y';

    -- Cursor left open for client application
    OPEN cursor1;
    SET SQLSTATE_OUT = SQLSTATE;
    SET SQLCODE_OUT = SQLCODE;


    SELECT I47_SRV_COOR_LEV,
    I48_POSITION_TYPE
    INTO SRV_COOR_LEV,
    POS_TYPE
    FROM SERDB.I47_SRVCOOR_LVL_T,
    SERDB.I48_CONS_WORKER_T
    WHERE I47_CASE_ID = CASE_ID
    AND I48_CASE_ID = I47_CASE_ID
    AND I47_SRVCOOR_END_DT IS NULL
    AND I48_WKER_END_DT IS NULL
    AND I48_PRIMARY_OTHER = 'P';

    SET FAILEDAT = 'STEP00';

    ***If the above select statement does not return a value (for example, an invalid case_id is entered) I want the stored procedure to stop at this point.

    -- IF NOTFOUNDIND = 'Y'
    -- THEN
    -- SELECT SQLSTATE, SQLCODE INTO SQLSTATE_OUT, SQLCODE_OUT
    -- FROM SYSIBM.SYSDUMMY1;
    -- END IF;

    IF POS_TYPE NOT IN ('A','C','H')
    OR SRV_COOR_LEV <> 1

    -- IF NOTFOUNDIND = 'N'
    -- AND (POS_TYPE NOT IN ('A','C','H') OR SRV_COOR_LEV <> 1)

    THEN
    UPDATE SERDB.I21_CONS_CASE_T
    SET I21_SINGLE_PLAN_DT = NULL,
    I21_PLAN_DUE_DT = NULL,
    I21_USER = NEWUSER
    WHERE I21_CASE_ID = CASE_ID;

    SET FAILEDAT = 'STEP01';
    END IF;

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Is it an option to declare an EXIT HANDLER for SQLSTATE '02000'? If not, then you could do this:
    Code:
    IF notfoundind = 'Y' THEN
       RETURN;
    END IF;
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Aug 2007
    Posts
    56
    Quote Originally Posted by stolze
    Is it an option to declare an EXIT HANDLER for SQLSTATE '
    02000'?

    **I don't understand what you're asking here.

    If not, then you could do this:
    Code:
    IF notfoundind = 'Y' THEN
       RETURN;
    END IF;

    **I tried the above code, however, I recieved the following error when attempting to build the stored procedure.

    CONVERT SQLPRINT 22 0DSNH104I E DSNHSMS1 LINE 77 COL 4 ILLEGAL SYMBOL "RETURN". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: <IDENTIFIER>
    CONVERT SQLPRINT 23 DSNH016I E DSNHPARS LINE 77 COL 10 ":" REQUIRED
    CONVERT SQLPRINT 24 DSNH104I E DSNHPARS LINE 77 COL 10 ILLEGAL SYMBOL ";". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: :

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Which DB2 version are you using?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Aug 2007
    Posts
    56
    Quote Originally Posted by stolze
    Which DB2 version are you using?
    I'm using 7.1 on z/os platform.

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Since you are on a rather backlevel version, I think your only option is to only proceed the work on success:
    Code:
    SELECT ...
    IF notfoundind <> 'Y' THEN
       ...here your regular processing ...
    END IF;
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Aug 2007
    Posts
    56
    Thanks, I will give this a try.

Posting Permissions

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