This is what we use.. But I suggest that you define the SQLSTATE and
SQLCODE variables and set errorcode not to 12345 but to sqlstate. Check
one of the links I gave you. And don't use the error handling check at
the bottom. Do whatever you want to as part of the exit handler. You can
use a compound statement for the exit handler. Use begin and end
keywords.


CREATE PROCEDURE EONE.pSample (IN vStatement VARCHAR(200), OUT
oReturncode SmallInt)
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
DECLARE ErrorCode Char(5) DEFAULT '00000' ;

-- can be commented if not required. Currently commented. Will need if
we are using cursors
-- DECLARE vAtEnd Char(1);

Error: BEGIN ATOMIC

DECLARE exit HANDLER FOR SQLEXCEPTION
SET ErrorCode = '12345';


-- can be commented if not required. Currently commented. Will need if
we are using cursors
-- DECLARE Continue HANDLER FOR NOT Found
-- SET vAtEnd = '1';

--Assumming that you want to ignore warnings
DECLARE Continue HANDLER FOR SQLWARNING
SET ErrorCode = '00000';

Execute Immediate vStatement ;

END ERROR;

--Error Handling

IF ErrorCode != '00000' THEN
ROLLBACK;
SET oReturnCode = 1;
ELSE
SET oReturnCode = 0;
COMMIT;
END IF;

END P1

Hope its of some help.

Will post a perfect use of error handling when I have more time on my
hands.

Regards,

Andy


-----Original Message-----
From: shomit [mailto:shomit.[Email address protected]
Sent: Thursday, August 31, 2006 1:08 PM
To: Kaushal, Anand
Subject: RE: Error handling

**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email, **and removed
any attachments, and kept your email address secret **from this person,
and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal **Anything below this line is the original email text


Thanks, I have checked the links..........these have been useful.

Actually I need to write an error handling strategy (with specific
examples) for SQL exceptions (row not found, constraint violation, etc)
and other business exceptions.

Any inputs...??


Thanks,
Shomit





"Kaushal Anand "
[Email address protected]
08/31/2006 10:31 AM

To
"LazyDBA Discussion" [Email address protected] cc

Subject
RE: Error handling






http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.
ibm.db2.udb.doc/ad/c0009027.htm
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.
ibm.db2.udb.doc/ad/c0009028.htm
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.
ibm.db2.udb.doc/admin/r0004240.htm
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.
ibm.db2.udb.doc/ad/c0011921.htm

I could go on and explain it, but I'm so hard pressed for time... Maybe
later.


Regards,

Anand.


-----Original Message-----
From: shomit
[mailto:db2udbdba-ezmlmshield-x7227430.[Email address protected]
Sent: Thursday, August 31, 2006 10:24 AM
To: LazyDBA Discussion
Subject: Error handling

Hi,

Can anybody tell me any good reference for error handling in stored
procedures.

Thanks,

Shomit Mohan
=====-----=====-----=====
Notice: The information contained in this e-mail message and/or
attachments to it may contain confidential or privileged information. If
you are not the intended recipient, any dissemination, use, review,
distribution, printing or copying of the information contained in this
e-mail message and/or attachments to it are strictly prohibited. If you
have received this communication in error, please notify us by reply
e-mail or telephone and immediately and permanently delete the message
and any attachments. Thank you




---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html





---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

ForwardSourceID:NT0000C1DA





DB2 & UDB email list listserv db2-l LazyDBA home page