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
浙公網(wǎng)安備 33010602011771號