oday 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.
As you can see, we again get two resultsets back with in-going and out-going values for @rc.
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.
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!
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.
Refer:
http://weblogs.sqlteam.com/peterl/archive/2009/04/07/ERROR-BEGIN-TRYCATCH-and-XACT_ABORT.aspx