Error Handling in SQL Server 2005

Just a quick note on Error Handling for people switching from SQL Server 2000 to SQL Server 2005. The new structured error handing mechanism provided in T-SQL 2005 provides a more simple way to handle exceptions in code. Let’s have a look at the following example which is catching a divide by zero error:

BEGIN TRY
SELECT 5/0
END TRY
BEGIN CATCH
PRINT 'Error Number =  ' + CONVERT(VARCHAR(10), Error_Number())
PRINT 'Error Message =  ' + Error_Message()
END CATCH

This is serving you with a well formed error message and a clean try/catch error handling as known from programming in .NET. Moreover these classes are available:

ERROR_NUMBER() – Number of error
ERROR_MESSAGE() – Error Message
ERROR_LINE() – Line on which error occurred
ERROR_PROCEDURE() – Procedure in which error occurred
ERROR_SEVERITY() – Severity of error
ERROR_STATE() – State of error
XACT_STATE() – Transaction state

Enjoy 🙂