Lemke IT Homepage

Special September 2017 - Exception handling in stored procedures
Category : User
Level : Advanced

Back to Overview

Database stored procedures are used to encapsulate database oriented algorithms and to store it into the database. Often this results in more efficient and smaller code instead of an implementation in the upper application. Also I like to use stored procedures to keep the application database independent. All database specific operations can be hidden in the stored procedure.

To get the full power of stored procedures, it is important to understand the concept of exception handling. This also helps to keep the above described advantages. To get in touch, we start with a very simple procedure, which just throws an expcetion.

drop if exists procedure excepCheck001; 
@ 
create procedure excepCheck001 ( ) 
begin 
   throw 'Ouups, an exception ..'; 
end; 
@

As expected, calling this procedure results in an exception

CGCLT > call excepCheck001(); 
Error : Procedure exception : 'Ouups, an exception ..' 
Query failed

Now we modify the implementation in a way, that we add a return value to the procedure and catch the user thrown exception. In case of an exception, a value of 1 is returned by the procedure now.

drop if exists procedure excepCheck002; 
@ 
create procedure excepCheck002 ( ) return int 
begin 
   throw 'Ouups, an exception ..'; 
   return 0; 
exception when other 
then 
   return 1; 
end; 
@

After the procedure has been loaded, it can be checked by executing it with the following commands

CGCLT > :r = call excepCheck002(); 
Procedure executed 
ok ( 0.002 s ) 
CGCLT > print :r; 
1 
ok ( 0.000 s )

If you want to provide the exception message to the calling application, this can be done by adding an out parameter to the procedure. If an exception is thrown, the exception message is stored in the special variable excep_info. In the exception block, this value is assigned to the out parameter

drop if exists procedure excepCheck003; 
@ 
create procedure excepCheck003 ( r out string(20) ) return int 
begin 
   throw 'Ouups, an exception ..'; 
   return 0; 
exception when other 
then 
   :r = :excep_info; 
   return 1; 
end; 
@

And here is, how to check it

CGCLT > :r = call excepCheck003(:s); 
Procedure executed 
ok ( 0.002 s ) 
CGCLT > print :r; 
1 
ok ( 0.000 s ) 
CGCLT > print :s; 
'Ouups, an exception ..' 
ok ( 0.000 s )

Exception also can be nested .....

drop if exists procedure excepCheck004; 
@ 
create procedure excepCheck004 ( i in int, r out string(20) ) return int 
begin 
   if :i = 1 
   then 
     begin 
       throw 'A nested exception ..'; 
     exception when other 
     then 
       :r = 'Exception for 1'; 
     end; 
   else 
     throw 'Ouups, an exception in the else path ..'; 
   end; 
   return 0; 
exception when other 
then 
   :r = :excep_info; 
   return 1; 
end; 
@ 

:r = call excepCheck004(1, :s); 
print :r; 
print :s; 
:r = call excepCheck004(2, :s); 
print :r; 
print :s;

And here ist the expected output of the batch

$ ./chkclt --batchfile=p4.sql 
Procedure excepCheck004 dropped 
ok ( 0.000 s ) 
Procedure excepCheck004 created 
ok ( 0.000 s ) 
Procedure executed 
ok ( 0.003 s ) 
0 
ok ( 0.000 s ) 
Exception for 1 
ok ( 0.000 s ) 
Procedure executed 
ok ( 0.000 s ) 
1 
ok ( 0.000 s ) 
'Ouups, an exception in the else path ..' 
ok ( 0.000 s )