SQL Language Guide : 7. Database Procedures, Sessions, and Events : How Database Procedures Are Created, Invoked, and Executed : Effects of Errors in Database Procedures
 
Share this page                  
Effects of Errors in Database Procedures
When an error occurs in a database procedure, it has the following effects:
• All statements in the procedure up to the point of the error are rolled back.
• The procedure continues execution with the statement following the statement that caused the error.
• Parameters passed by reference are not updated.
The error is returned to the application in SQLSTATE, SQLCODE, and ERRORNO. An error number is also returned to iierrornumber, a built-in variable available only in database procedures for error handling.
Note:  A fatal error, such as dividing by zero, cannot be trapped in a database procedure and will cause the procedure to fail. For example, executing the following database procedure, in which the divisor variable is 0, will result in program failure:
...
update a_test set an_integer=int4(an_integer/:divisor);
select iierrornumber, iirowcount into :enumber, :rowcount;
if enumber=0 then
if rowcount=0 then
...
iierrornumber and iirowcount Variables
The iierrornumber and iirowcount variables, in conjunction with the RAISE ERROR statement, handle errors in database procedures.
The iirowcount variable contains the number of rows affected by the last executed SQL statement. The iierrornumber variable contains the error number (if any) associated with the execution of a database procedure statement.
Because both iierrornumber and iirowcount reflect the results of the preceding query, beware of inadvertently resetting the value of one when checking the other.
The following example from a database procedure illustrates this error:
...

update emp set ...

/* The following statement resets iierrornumber, which will reflect the results of the second statement and not the first, as desired. */

/* wrong way to check iirowcount */

rcount = iirowcount;

/* The error number reflects the results of the preceding assignment, not the update statement */

enumber = iierrornumber;
The following example illustrates the correct way to check iierrornumber and iirowcount: select both values into variables, and then check the contents of the variables (because iierrornumber and iirowcount is reset to reflect the results of the SELECT statement).
...

update emp set ...

/* right way to check iirowcount (using select) */

select iirowcount, iierrornumber into rcount, enumber;
 
The following table lists the values of iirowcount and iierrornumber after the successful or unsuccessful execution of an SQL statement:
Statement
Success
Success
Error
Error
iirowcount
iierrornumber
iirowcount
iierrornumber
Insert
number of rows
0
0
error number
Update
number of rows
0
0
error number
Delete
number of rows
0
0
error number
Select
0 or 1
0
0
error number
Assignment
1
0
0
error number
Commit
-1
0
-1
error number
Rollback
-1
0
-1
error number
Message
-1
0
-1
error number
Return
-1
0
-1
error number
If
no change
no change
no change
error number
Elseif
no change
no change
no change
error number
While
no change
no change
no change
error number
Else
no change
no change
no change
no change
Endif
no change
no change
no change
no change
Endloop
no change
no change
no change
no change
Endwhile
no change
no change
no change
no change
The execution of each database procedure statement sets the value of iierrornumber either to zero (no errors) or an error number. To check the execution status of any particular statement, iierrornumber must be examined immediately after the execution of the statement.
Errors occurring in IF, WHILE, MESSAGE, and RETURN statements do not set iierrornumber. However, any errors that occur during the evaluation of the condition of an IF or WHILE statement terminate the procedure and return control to the calling application.
Raise Error Statement
The RAISE ERROR statement generates an error. The DBMS Server responds to this error exactly as it does to any other error. If the RAISE ERROR statement is issued by a database procedure that is directly executed, the error is handled using the default error handling behavior or the user-supplied error handling mechanism.
The error number that is specified as an argument to raise error is returned to sqlerrd(1), and can be accessed using INQUIRE_SQL(DBMSERROR).