Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

@@ERROR, BEGIN TRY/CATCH and XACT_ABORT

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
GO
CREATE PROCEDURE uspTest_2000
AS
 
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
GO
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
GO
CREATE PROCEDURE uspTest_2005
AS
 
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
GO
 
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
GO
CREATE PROCEDURE uspTest_2008
AS
 
SET XACT_ABORT ON
 
CREATE TABLE          #Sample
                      (
                                 i TINYINT
                      )
 
INSERT     #Sample
SELECT     209
 
DROP TABLE #Sample
GO
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
GO
CREATE PROCEDURE uspTest_2000
AS
 
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
GO
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
GO
CREATE PROCEDURE uspTest_2005
AS
 
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
GO
 
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
GO
CREATE PROCEDURE uspTest_2008
AS
 
SET XACT_ABORT ON
 
CREATE TABLE          #Sample
                      (
                                 i TINYINT
                      )
 
INSERT     #Sample
SELECT     2090
 
DROP TABLE #Sample
GO
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.
 

Print | posted on Tuesday, April 07, 2009 9:53 AM | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]

Feedback

Gravatar

# re: @@ERROR, BEGIN TRY/CATCH and XACT_ABORT

XACT_ABORT has been around since at least SQL Server 2000
4/8/2009 8:23 AM | gbn
Gravatar

# re: @@ERROR, BEGIN TRY/CATCH and XACT_ABORT

Its very nice
but i dont know abt

SET XACT_ABORT ON
9/1/2009 9:19 AM | very nice
Gravatar

# re: @@ERROR, BEGIN TRY/CATCH and XACT_ABORT

thanks for article
12/31/2009 7:16 AM | venkat
Gravatar

# re: @@ERROR, BEGIN TRY/CATCH and XACT_ABORT

thanks for article.
Its very nice.
explain about

SET XACT_ABORT ON
12/31/2009 7:44 AM | venkat
Gravatar

# re: @@ERROR, BEGIN TRY/CATCH and XACT_ABORT

Very nice article.
11/1/2012 6:47 PM | SQLFan
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET