SQL Server – Error Handling Using TRY…CATCH

Varinder Sandhu SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

Prior to SQL server 2005, the only way of performing the error handling was to test vale of @@ERROR global variable. Once the SQL Server completes the execution of a Transact-SQL statement, it records the status of result in @@ERROR. If the error occurred, @@ERROR contains the error number. If the statement executed successfully it contain the 0. You need to check the variable if the statement succeeded or failed.

Using @@ERROR to perform the error handling is not easy because you need to check after each statement.

But in SQL 2005 onwards, Microsoft provide a more structured way to perform the error handling that is very similar to error handling routine to other programming languages. You can now use the TRY…CATCH block.

The TRY…CATCH block has two components. A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is passed to another group of statements that is enclosed in a CATCH block.

Example Script:

USE testdb
GO

-- Verify that the stored procedure does not exist.
IF OBJECT_ID ( N'sp_ExampleProc', N'P' ) IS NOT NULL
    DROP PROCEDURE sp_ExampleProc
GO

-- Create a stored procedure that will cause an
-- object resolution error.
CREATE PROCEDURE sp_ExampleProc
AS
    SELECT * FROM NonexistentTable
GO

BEGIN TRY
    EXECUTE sp_ExampleProc
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage
END CATCH

In the scope of a CATCH block, the following system functions can be used to obtain information about the error that caused the CATCH block to be executed:

ERROR_NUMBER() returns the number of the error.
ERROR_SEVERITY() returns the severity.
ERROR_STATE() returns the error state number.
ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
ERROR_LINE() returns the line number inside the routine that caused the error.
ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

These functions return NULL if they are called outside the scope of the CATCH block. Error information can be retrieved by using these functions from anywhere within the scope of the CATCH block.