Best Practices for Error Handling and Defensive Programming in SQL Server

This session discussed Error Handling in SQL Server 2005

Types of SQL Server Errors/Exceptions:
Statement Terminating
Batch Aborting
Connection Aborting
Service Terminatin

SQL Server Error Severity:
Error <10 is just an error not severe at all
Error >= 11 Exceptions
Error >= 24 are Severe

XACT_ABORT: Turns stement termination into batch abortion

Error Number (somewhat importing)
Error Severity (important)
Error State (not important at all)
Error Procedure Name
Error Line Number
Error Message

Way of displaying Errors in SQL Server
The RAISERROR Statement

Exception Handling
Use to be @@ERROR in SQL Server 2000

In SQL Server 2005
BEGIN TRY
Select * from Sales
END TRY

BEGIN CATCH
Select ERROR_NUMBER
END CATCH

TRY CATCH FUNCTION:
ERROR_NUMBER
ERROR_MESSAGE
ERROR_SEVERITY
ERROR_STATE
ERROR_LINE

Summary: The Try Catch is the big change in SQL Server 2005/2008 and replaces the @@ERROR. Not much here that we did not already know.