: SQL - Error HandlingAug 10 2011, 19:00 PM

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…