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.
print 'test @@error'
if (@@error = 8134)
print 'Error Encountered'
print 'Execution Continues'
Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.
There are two drawbacks:
- 'Statement Abort' as an error policy is rarely found in a procedural language. This is counter-intuitive.
- 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.
print ' test tsql try-catch'
print 'Execution Halts'
print ' error caught'
test tsql try-catch
(0 row(s) affected)
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.