Error Handling in SQL Server with Try Catch

An exception is defined as a runtime error that can be handled programmatically. If not handled, it might terminate the flow of a program. Modern day programming languages like C#, Java, etc., have built-in support for exception handling. SQL server is adept at error handling as well – it has built-in support for constructs like TRY, CATCH that can be used to handle errors elegantly. Incidentally the THROW statement was introduced in SQL server 2012 and its inclusion makes error handling in SQL server much easier. This article talks about how you can handle errors in SQL server using TRY..CATCH statements

Error Handling in SQL Server

Here’s how error handling in SQL server works. In SQL server you can take advantage of TRY..CATCH statements to handle errors. When writing code that handles errors, you should have a TRY block and a CATCH block immediately after it. The TRY block starts with a BEGIN TRY statement and ends with an END TRY statement. Similarly, that CATCH blocks starts with a BEGIN CATCH statement and ends with an END CATCH statement.

Assume that there are a group of statements in the TRY block, i.e., enclosed between the BEGIN TRY .. END TRY statements. As soon as an error occurs in this TRY block, the control moves to the CATCH block where you might have another group of statements for handling errors. In this regard, the following points should be noted:

A TRY block should be immediately be followed by a CATCH block where the error handling code resides. Here’s an example code that illustrates this.

BEGIN TRY
    -- write statements here that may cause exception
END TRY
BEGIN CATCH
    -- write statements here to handle exception
END CATCH

When an error occurs inside the TRY block, the control moves to the first statement inside the CATCH block. On the contrary, if the statements inside a TRY block have completed execution successfully without an error, the control will not flow inside the CATCH block. Rather, the first statement immediately after the END CATCH statement will then be executed.

Retrieving detailed information on the error

you can take advantage of various functions inside the CATCH block to get detailed information about an error.

These functions include the following:

• ERROR_MESSAGE() – you can take advantage of this function to get the complete error message
• ERROR_LINE() – this function can be used to get the line number on which the error occurred
• ERROR_NUMBER() – this function can be used to get the error number of the error
• ERROR_SEVERITY() – this function can be used to get the severity level of the error
• ERROR_STATE() – this function can be used to get the state number of the error
• ERROR_PROCEDURE() – this function can be used to know the name of the stored procedure or trigger that has caused the error