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.
[syntax_prettify linenums=”linenums”]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’[/syntax_prettify]

Note that this behavior can also be triggered manually, via the RAISERROR command (notice the spelling, with only one E):
[syntax_prettify linenums=”linenums”]RAISERROR(‘Fatal error’,16,1)[/syntax_prettify]

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…