SQL – Error Handling

Yet another “new” feature of MS SQL 05 is an often overlooked “error handling”. The Transact-SQL Try…Catch is designed to operate similar to the exception handler in the Visual/.NET languages- if an error occurs inside the TRY block of a query, control is passed to another group of statements that is enclosed in a CATCH block (and on)… Or so it should- the handler will not help with errors of severity 20+, KILLs, and, of course, with various warnings and messages, so be careful and remember to test all of the scenarios right away.

BEGIN TRY
 SELECT 1/0
 END TRY
 BEGIN CATCH
 SELECT
 ERROR_NUMBER() AS ErrorNumber,
 ERROR_SEVERITY() AS ErrorSeverity,
 ERROR_STATE() AS ErrorState,
 ERROR_PROCEDURE() AS ErrorProcedure,
 ERROR_LINE() AS ErrorLine,
 ERROR_MESSAGE() AS ErrorMessage
 END CATCH
 SELECT 'Continue the run'

Note that this behavior can also be triggered manually, via the RAISERROR command (notice the spelling, with only one E):

RAISERROR('Fatal error',16,1)

At last, sometimes, when using this functionality, you might need to ensure that you do exit and don’t run the rest of the statements- feel free to add RETURN command in such cases…