Server Side Error Handling – Part 1 (Migrating from @@error to tsql try-catch)


Tsql try-catch was added to improve server side error handling in sql server 2005. This feature should have been part of early T-Sql. Better late than never.


In absence of tsql try-catch, server-side error handling was done using @@error. This had necessitated statement abort as the error policy. Under this policy the execution of a batch/procedure continues after full or partial rollback of the tsql statement that raised an error.


Command:




print ‘test @@error’
select 4/0
if (@@error = 8134)
 print ‘Error Encountered’
print ‘Execution Continues’


Output:




test @@error


———–
Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.


Error Encountered
Execution Continues


There are two drawbacks:



  1. ‘Statement Abort’ as an error policy is rarely found in a procedural language. This is counter-intuitive. 

  2. Clients do not expect to see any error message if an error is handled at the server.


The above batch can be replaced using tsql try-catch.


Command:




begin Try
      print ‘ test tsql try-catch’
      select 4/0
      print ‘Execution Halts’
end try
begin catch
      print ‘ error caught’
end catch


Output:




 test tsql try-catch


———–


(0 row(s) affected)


 error caught


There is no ‘Statement Abort’ inside a tsql try-catch. Error is handled at the server and no error message is relayed to the client. The execution inside tsql try-catch stops on an error and the catch block is activated.


For backwad compatibility we still have ‘statement abort’ as an error policy. Moving forward there is a chance that it might be deprecated.


In next post, I will discuss why some errors are not caught (either sent to the client or ignored silently) by tsql try-catch.


Thanks

Comments (0)