@@ERROR, BEGIN TRY/CATCH and XACT_ABORT

Tue Apr 7, 2009 by Peter Larsson in sql-server-2008, administration, sql-server-2005, sql-server-2000

Today I am showing you the difference between @@ERROR, BEGIN TRY/CATCH and XACT_ABORT.
The three alternatives to error handling works little different and let us intercept the error handling in different stages in the process.

First I am going to show you the ordinary @@ERROR check which most of you are used to.

IF OBJECT_ID('uspTest_2000') IS NOT NULL

DROP PROCEDURE uspTest_2000

CREATE PROCEDURE uspTest_2000

CREATE TABLE #Sample

(

i TINYINT

)

BEGIN TRANSACTION

INSERT #Sample

SELECT 209

IF @@ERROR <> 0

BEGIN

ROLLBACK TRANSACTION

PRINT 'Insert Error (User defined error message)'

END

ELSE

BEGIN

COMMIT TRANSACTION

PRINT 'Insert OK (User defined error message)'

END

DROP TABLE #Sample

DECLARE @rc INT

SELECT @rc AS rc,

@@TRANCOUNT AS [TransactionCount]

EXEC @rc = uspTest_2000

SELECT @rc AS rc,

@@TRANCOUNT AS [TransactionCount]

As you can see, the code works ok and no error is generated. You also get two resultsets back with in-going and out-going value for @rc variable.

In SQL Server 2005 BEGIN TRY/CATCH was introduced and can be written like this.

IF OBJECT_ID('uspTest_2005') IS NOT NULL

DROP PROCEDURE uspTest_2005

CREATE PROCEDURE uspTest_2005

CREATE TABLE #Sample

(

i TINYINT

)

BEGIN TRY

BEGIN TRANSACTION

INSERT #Sample

SELECT 209

COMMIT TRANSACTION

PRINT 'Insert OK (User defined error message)'

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION

PRINT 'Insert Error (User defined error message)'

PRINT ERROR_MESSAGE()

END CATCH

DROP TABLE #Sample

DECLARE @rc INT

SELECT @rc AS rc,

@@TRANCOUNT AS [TransactionCount]

EXEC @rc = uspTest_2005

SELECT @rc AS rc,

@@TRANCOUNT AS [TransactionCount]

As you can see, we again get two resultsets back with in-going and out-going values for @rc.

In SQL Server 2005 and SQL Server 2008 we also have the option of XACT_ABORT.

You can see here how that is written.

IF OBJECT_ID('uspTest_2008') IS NOT NULL

DROP PROCEDURE uspTest_2008

CREATE PROCEDURE uspTest_2008

SET XACT_ABORT ON

CREATE TABLE #Sample

(

i TINYINT

)

INSERT #Sample

SELECT 209

DROP TABLE #Sample

DECLARE @rc INT

SELECT @rc AS rc,

@@TRANCOUNT AS [TransactionCount]

EXEC @rc = uspTest_2008

SELECT @rc AS rc,

@@TRANCOUNT AS [TransactionCount]

And again we get two resultset back with in-going and out-going values for @rc.
So far so good.

The interesting part begins when error occurs. We can easily produce an error by inserting the value of 2090 instead of 209 in the SMALLINT column.

IF OBJECT_ID('uspTest_2000') IS NOT NULL

DROP PROCEDURE uspTest_2000

CREATE PROCEDURE uspTest_2000

CREATE TABLE #Sample

(

i TINYINT

)

BEGIN TRANSACTION

INSERT #Sample

SELECT 2090

IF @@ERROR <> 0

BEGIN

ROLLBACK TRANSACTION

PRINT 'Insert Error (User defined error message)'

END

ELSE

BEGIN

COMMIT TRANSACTION

PRINT 'Insert OK (User defined error message)'

END

DROP TABLE #Sample

DECLARE @rc INT

SELECT @rc AS rc,

@@TRANCOUNT AS [TransactionCount]

EXEC @rc = uspTest_2000

SELECT @rc AS rc,

@@TRANCOUNT AS [TransactionCount]

Yes, we do get two resultsets back, and we also get two error messages!

SQL Server delivers a collection of error messages back to the client! This collection has two error messages; first one for SQL Server internal and the other is the user defined error message.

Msg 220, Level 16, State 2, Procedure uspTest_2000, Line 11

Arithmetic overflow error for data type tinyint, value = 2090.

The statement has been terminated.

Insert Error (User defined error message)

What happens then with BEGIN TRY/CATCH?

IF OBJECT_ID('uspTest_2005') IS NOT NULL

DROP PROCEDURE uspTest_2005

CREATE PROCEDURE uspTest_2005

CREATE TABLE #Sample

(

i TINYINT

)

BEGIN TRY

BEGIN TRANSACTION

INSERT #Sample

SELECT 2090

COMMIT TRANSACTION

PRINT 'Insert OK (User defined error message)'

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION

PRINT 'Insert Error (User defined error message)'

PRINT ERROR_MESSAGE()

END CATCH

DROP TABLE #Sample

DECLARE @rc INT

SELECT @rc AS rc,

@@TRANCOUNT AS [TransactionCount]

EXEC @rc = uspTest_2005

SELECT @rc AS rc,

@@TRANCOUNT AS [TransactionCount]

The big difference is that now the internal error message provided by SQL Server is not displayed automatically!

Luckily we also have more error function to use besides the one you see in the code above; ERROR_MESSAGE.
One of those is named ERROR_LINE which gives you the line number for the statement generating the error!

With BEGIN TRY/CATCH we have the option to decide which error message to display and in which order.

Insert Error (User defined error message)

Arithmetic overflow error for data type tinyint, value = 2090.

How does then XACT_ABORT work?

IF OBJECT_ID('uspTest_2008') IS NOT NULL

DROP PROCEDURE uspTest_2008

CREATE PROCEDURE uspTest_2008

SET XACT_ABORT ON

CREATE TABLE #Sample

(

i TINYINT

)

INSERT #Sample

SELECT 2090

DROP TABLE #Sample

DECLARE @rc INT

SELECT @rc AS rc,

@@TRANCOUNT AS [TransactionCount]

EXEC @rc = uspTest_2008

SELECT @rc AS rc,

@@TRANCOUNT AS [TransactionCount]

The big difference is that we don't have to explicit handle our transactions. SQL Server automatically does a rollback.

The other difference is that all code after the error is skipped.

You can tell due to now there is only one resultset which contains the in-going value for @rc.

Legacy Comments


gbn 2009-04-08	re: @@ERROR, BEGIN TRY/CATCH and XACT_ABORT XACT_ABORT has been around since at least SQL Server 2000

very nice 2009-09-01	re: @@ERROR, BEGIN TRY/CATCH and XACT_ABORT Its very nice but i dont know abt SET XACT_ABORT ON

venkat 2009-12-31	re: @@ERROR, BEGIN TRY/CATCH and XACT_ABORT thanks for article

venkat 2009-12-31	re: @@ERROR, BEGIN TRY/CATCH and XACT_ABORT thanks for article. Its very nice. explain about SET XACT_ABORT ON

SQLFan 2012-11-01	re: @@ERROR, BEGIN TRY/CATCH and XACT_ABORT Very nice article.