Re: Stored Procedure Errors

Simmons, Keith wrote: > All > > The subject may be misleading in this case, but here goes. > > IDS 7.31.FD6X1 running on SunOS 5.8 Generic_108528-20 sparc. > > I'm writing my first stored procedure and have sorted out the error trapping > using 'on exception', but what I want to do is to return a row detailing > that no rows were found i.e. I want to trap 'error' 100. Is this possible > within SPL or is 100 recognised as a valid correct status and not trappable? > > code snippet:- > BEGIN > ON EXCEPTION SET esql, eisam -- Catch all > errors > IF esql = 100 THEN -- > No rows found > LET w_x = "NoOrder"; > RETURN w_x, w_y; > ELSE > LET w_orderno = "SQLError"; > LET w_x = esql; > LET w_y = eisam; > RETURN w_x, w_y; > END IF > END EXCEPTION > FOREACH > SELECT x > , y > INTO w_x > , w_y > FROM line > WHERE a = w_x> > RETURN w_x, w_y WITH RESUME; > > END FOREACH > END > > This simply returns 'No Rows Found' rather than a single row containing > 'NoOrder', '' IIRC, only negative error numbers raise an exception. Error 100 is simply a warning, and you will need to check for it. Probably the simplest method is to count the rows returned by your FOREACH loop and check for a zero count at the end. If you get zero rows, then you could explicitly RAISE an EXCEPTION, or RETURN values as you are attempting. Although I would have thought that returning an error would be easier to detect by the calling process. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+ sending to informix-list