Re: Error handling in SQL Stored Procedure

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

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