Re: Error handling in SQL Stored Procedure

Hi Kurt

Is this an SQL procedure or an external one? I'm working on an SQL one, and that's what I'll use as context here.

The SQL Reference isn't much help, IMHO - it's not organized around actually writing a stored procedure.

The SQL Programming manual has some examples in its section on CREATE PROCEDURE. These use things like DECLARE CONTINUE HANDLER FOR and DECLARE EXIT HANDLER FOR. It also speaks of various control statements, such as SIGNAL and RESIGNAL.

Everything centers around SQLSTATE and SQLCODE, which are available easily in SQL procedures, I believe.

The embedded equivalent of the DECLARE ... HANDLER is WHENEVER, it seems - that is usually something whose use is discouraged, as I recall - instead, use SQLSTATE and/or SQLCODE to condition things.

I'm talking as a bit of a newbie in this realm, so please verify anything said here.

Oh yeah, there's always the Redbook on Stored procedures and UDFs and triggers - something like that - some examples I ran across - but it seems it's mostly get used to the structure, then do whatever statements work, right?

HTH
Vern

On 3/21/2013 3:44 PM, Anderson, Kurt wrote:

I'm at IBM i 7.1 and I just created a stored procedure to insert a record into a file (and return the identity created). However, I'm not sure how to apply error handling (to monitor for such things as a duplicate record error). I ran a bunch of searches, but I must not be using the right words. I've looked for Exception, Error handling, try, catch, and monitor.
I'm using this document as my main source of info: http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafz.pdf

Can anyone show me the path toward error handling in SQL on the IBM i?

Thanks,
Kurt