Re: Stored Procedure error trapping advice

Ed Schaefer wrote: > > Hi: > > I'm fairly new at stored procedures and was wondering if someone could > answer some questions. > > I've written a stored procedures which copies and deletes lots of data. > Comprised of a number of "insert into table_name select * from > another_table" type statements. > > 1) Can you use the DBINFO function to trap for an insert of delete > error? AFAIK there are just a few sqlca.sqlerrd[] components which are supported, yet. For instance, you can find out the number of rows processed after an insert/update or delete. > 2) What's the best way of using the 'on exception' clause to ignore > certain errors as non-fatal? I guess you want to ignore certain errors while the server is processing the statements so that the statements shall not be interrupted, don't you ? Sure, you can change the default behaviour of the SP by adding the "WITH RESUME" clause at the end of your ON EXCEPTION statement. This will prevent your SP from terminating in case of an error, but the statements itself will be terminated when the server detects a fatal error. On the one hand your "insert into table select ..." statement is a critical one, because it will be terminated if either the select or the insert part will result in an error. On the other hand, what might happen ( with the exception of a locking problem, which can be handled by "isolation levels" and/or "set lock mode" ) if you wrote an ordinary select statement ? Maybe it will help if you tell us what kind of errors are "non-fatal" ? Bye Stefan Weideneder > any help is appreciated. > > regards, > > Ed > Schaefer