Handling SQL Server Errors

By Bill Graziano on 5 April 2010 | Tags: Stored Procedures , SQL Server 2005 , Transactions , SQL Server 2008 Features , Error Handling


This article covers the basics of TRY CATCH error handling in T-SQL introduced in SQL Server 2005. It includes the usage of common functions to return information about the error and using the TRY CATCH block in stored procedures and transactions.

SQL Server uses the following syntax to capture errors in Transact-SQL statements:

BEGIN TRY
    SELECT [First] = 1
    SELECT [Second] = 1/0
    SELECT [Third] = 3
END TRY
BEGIN CATCH
    PRINT 'An error occurred'
END CATCH

This returns the following output:

First
-----------
1

(1 row(s) affected)

Second
-----------

(0 row(s) affected)

An error occurred

A key difference from SQL Server 2000 is that execution is halted when SQL Server encounters an error.  At that point execution transfers to the CATCH block.  This error isn't returned to the client application or calling program.  The TRY CATCH block consumes the error.

Error Functions

Inside the CATCH block there are a number of specialized functions to return information about the error.

BEGIN TRY
    SELECT [Second] = 1/0
END TRY
BEGIN CATCH
    SELECT [Error_Line] = ERROR_LINE(),
           [Error_Number] = ERROR_NUMBER(),
           [Error_Severity] = ERROR_SEVERITY(),
           [Error_State] = ERROR_STATE()

    SELECT [Error_Message] = ERROR_MESSAGE()
END CATCH
Second
-----------

(0 row(s) affected)

Error_Line  Error_Number Error_Severity Error_State
----------- ------------ -------------- -----------
2           8134         16             1

(1 row(s) affected)

Error_Message
---------------------------------------------------
Divide by zero error encountered.

(1 row(s) affected)

These are the functions you can use inside a CATCH block.  These functions all return NULL if they are called from outside a CATCH block.

  • ERROR_NUMBER.  The number of the error that occurred.  This is similar to @@ERROR except that it will return the same number for the duration of the CATCH block.
  • ERROR_MESSAGE.  The complete text of the error message including any substiture parameters such as object names.
  • ERROR_LINE.  This is the line number of the batch or stored procedure where the error occured.
  • ERROR_SEVERITY.  This is the severity of the error.  The CATCH block only fires for errors with severity 11 or higher.  Error severities from 11 to 16 are typically user or code errors. Severity levels from 17 to 25 are usually software or hardware errors where processing may not be able to continue.
  • ERROR_STATE.  This is sometimes used by the system to return more information about the error.
  • ERROR_PROCEDURE.  If the error was generated inside a stored procedure this will hold the name of the procedure.

Trapping Errors in Stored Procedures

A TRY CATCH block can catch errors in stored procedures called by other stored procedures.  An example is:

BEGIN TRY
    EXEC ParentError
END TRY
BEGIN CATCH
    SELECT Error_Line = ERROR_LINE(),
           Error_Proc = ERROR_PROCEDURE()
END CATCH

Assuming that the ParentError stored procedure calls the ChildError stored procedure which generates an error you'd see output like this:

Error_Line  Error_Proc
----------- -------------
5           ChildError

Error Handling and Transactions

 The TRY CATCH syntax also works well with transactions.  Below is a common pattern used inside stored procedures for transactions.  Any errors cause the transaction to roll back.

BEGIN TRY
    BEGIN TRANSACTION

    INSERT INTO dbo.invoice_header
        (invoice_number, client_number)
    VALUES (2367, 19)

    INSERT INTO dbo.invoice_detail
        (invoice_number, line_number, part_number)
    VALUES (2367, 1, 84367)

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF @@TRANCOUNT() > 0 ROLLBACK TRANSACTION
    -- And do some cool error handling
END CATCH

 


Related Articles

Testing with Profiler Custom Events and Database Snapshots (22 June 2009)

Advanced SQL Server 2008 Extended Events with Examples (25 May 2009)

Introduction to SQL Server 2008 Extended Events (19 May 2009)

Monitoring SQL Server Agent with Powershell (24 March 2009)

SQL Server 2008: Table Valued Parameters (24 July 2008)

Using the TIME data type in SQL Server 2008 (6 March 2008)

Using the DATE data type in SQL Server 2008 (6 December 2007)

Transaction Isolation and the New Snapshot Isolation Level (9 July 2007)

Other Recent Forum Posts

Performance tuning (4h)

As I gain experience and get older, I'm working much slower, but producing better quality, but (7h)

Master DB 2019 problem (22h)

Please help, I can't login remote to MS SQL 2019 without sysadmin role (1d)

SSMS Cannot Connect to Newly Installed Instance (2017) (1d)

SQL server 2019 alwayson problem (2d)

Finding Possible Duplicates (4d)

SQL Agent Service will not start - timeout error (5d)

- Advertisement -