Using Try-Catch in Transact-SQL

SQL 2005 implemented the TRY-CATCH form of trapping errors in SQL code. The old ON_Error statement no longer applies. This new construct is used in many languages today including C# and VB.NET to name a few. The basic concept is that you place your code within a Try and then use a Catch to find errors it may have encountered. Here is a very simple example you can run:

/* Simple Try Catch Example */
DECLARE @Denominator INT
SET @Denominator = 0

BEGIN TRY
SELECT 1/@Denominator AS [Output]
SELECT 'Computation did not fail' AS [message]
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS Error#, ERROR_MESSAGE() AS [MESSAGE];
GOTO EXITOR
END CATCH;
SELECT 'Catch Did Not Find Error'
EXITOR:


Now set the value of @Denominator to 1 or some other valid value. Notice how the flow of program execution changes.