Best Practices for Exception Handle and Defensive Programming in SQL Server

• Statement Terminating Error – On Error Resume Next
• Batch Aborting – anything before the GO is aborted
• Connection Aborting
• Everything is classified with Severities

Severity <10 – just a warning

Severity  -11 – Exceptions

11-16 Errors that can be corrected

Severity 17 – 19 Resource Issues – Running out of space

Severity 20 and above – Drop everything and fix.

• Most exceptions are stement-terminatin, irrespective of severity  - XACT_ABORT: turns statements into batch terminating as opposed to statement terminating. Recommended that we turn this on in all stored procedures that has more than on batch(GO).  Only on procedures that do not use TRY/CATCH.
• Sys.Messages
• Error Format: Error Number, Error Severity, Error State, Procedure Name, Line Number,Message(Most Useful)
• RAISERROR – raise an error, specify severity, state and other:

Example: RAISERROR(‘General Exception’, 16,1)

Can create custom RAISERROR’s

• Exception Handling -  this is TRY CATCH

Error Functions that can be used in the TRY CATCH:

ERROR_NUMBER

ERROR_MESSAGE

ERROR_SEVERITY

ERROR_STATE

ERRRO_LINE

• Transactions and Exceptions

Statement Transactions do not rollback

Batch Transactions do rollback.

• Conclusion:  We seem to be handling our error handling properly.  The speaker had some good examples, so we should link to his BLOG for some new idea.  Especially, the items he described on Retry Error blocks.