Freigeben über

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

  • Artikel

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