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;

Is it an option to declare an EXIT HANDLER for SQLSTATE '02000'? If not, then you could do this:

**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: :

Which DB2 version are you using?

I'm using 7.1 on z/os platform.

Since you are on a rather backlevel version, I think your only option is to only proceed the work on success:

Thanks, I will give this a try.