BE PREPARED!
(@@ERROR versus XACT_ABORT)
Be Prepared! Have you ever been a scout boy? That’s the right motto for scouts and for SQL Server developers in case of implementing error handling strategy. I never know where things can go if code in stored procedures isn’t full with a lot of “IF @ERROR<>0” statements.
Some better winds comes with Yukon. Like in .NET programming languages, Yukon has a structured exception handling.
--------------------------------------------------------------------------------------------- BEGIN TRY BEGIN Transaction SELECT 'error simulation' / 0 COMMIT Transaction END TRY BEGIN CATCH TRAN_ABORT DECLARE @err int SELECT @err = @@error PRINT '@@error: ' + ltrim(str(@err)) ROLLBACK END CATCH ---------------------------------------------------------------------------------------------
But till Yukon comes, we have to be prepared to set up errors ambush just using @@ERROR and RETURN parameter.
We have one more tool to beat errors, XACT_ABORT. If I execute SET XACT_ABORT ON, I will force atomicity in transaction, and rolling back every open transaction in case of “any error”. But XACT_ABORT ON is incapable in case of errors which source comes from Diferred Name Resolution.
When a stored procedure is executed for the first time, the query processor reads the text of the stored procedure from the syscomments system table of the procedure and checks that the names of the objects used by the procedure are present. This process is called deferred name resolution because objects referenced by the stored procedure need not exist when the stored procedure is created, but only when it is executed.
When a stored procedure is created it is allowed to use non existing table name. So a “typing error” in object name will pass without any warning. In addition, if you imagine that many languages have similar letters, but in different ASCII code then these errors became very dangerous.
For example, in the Macedonian language (using Windows Cyrillic, CodePage 1251)
---------------------------------------------------------------------------------------------
'T' <> 'T'.
SELECT ASCII ('T') English – Latin T
------------------
84
SELECT ASCII ('Т') Macedonian – CyrilicT
------------------
210
---------------------------------------------------------------------------------------------
Object names are checked in resolution stage. If an object does not exist (or the name is not correctly written), the error will fire.
Example 1: SET XACT_ABORT OFF / ON - Error simulation"Non existing table"
---------------------------------------------------------------------------------------------
CREATE TABLE [TabExist] ([p1] int,[p2] [char] (16)) ON [PRIMARY]
GO
CREATE PROCEDURE ProcAbortSample AS
INSERT INTO TabExist Values(1,'ProcAbortSample')
INSERT INTO TabNonExist Values(1,'ProcAbortSample')
INSERT INTO TabExist Values(2,'ProcAbortSample')
GO
CREATE PROCEDURE ProcCallerSample AS
DECLARE @Ret INT
BEGIN TRANSACTION
INSERT INTO TabExist Values('1','ProcCallerSample')
SET @Ret = 1001
EXEC @Ret = ProcAbortSample
PRINT 'Return parameter of ProcAbortSample is: ' + str(@Ret)
PRINT 'IF Return parameter after exec ProcAbortSample is 1001 Return is not affected'
COMMIT TRANSACTION
GO
DECLARE @RetFinal INT
DELETE FROM TabExist
SET XACT_ABORT OFF
EXEC @RetFinal = ProcCallerSample
PRINT 'Return parameter of ProcCallerSample is ' + str(@RetFinal)
GO
PRINT 'IF next <SELECT * FROM TabExist> yield any row then
INS statements are not rolled back'
SELECT * FROM TabExist
---------------------------------------------------------------------------------------------
Result…
Server: Msg 208, Level 16, State 1, Procedure ProcAbortSample, Line 3 Invalid object name 'TabNonExist'. Return parameter of ProcAbortSample is: 1001 IF Return parameter after exec ProcAbortSample is 1001 Return is not affected Return parameter of ProcCallerSample is 0 IF next <SELECT * FROM TabExist> yield any row then INS statements are not rolled back p1 p2 ----------- ---------------- 1 ProcCallerSample 1 ProcAbortSample ---------------------------------------------------------------------------------------------
This is OK because I executed <SET XACT_ABORT OFF>. But if I’m going to execute the same batch with SET XACT_ABORT ON, the result will be the same.
--------------------------------------------------------------------------------------------- DECLARE @RetFinal INT DELETE FROM TabExist SET XACT_ABORT ON EXEC @RetFinal = ProcCallerSample PRINT 'Return parameter of ProcCallerSample is ' + str(@RetFinal) GO PRINT 'IF next <SELECT * FROM TabExist> yield any row then INS statements are not rolled back' SELECT * FROM TabExist ---------------------------------------------------------------------------------------------
Result…
Server: Msg 208, Level 16, State 1, Procedure ProcAbortSample, Line 3 Invalid object name 'TabNonExist'. Return parameter of ProcAbortSample is: 1001 IF Return parameter afrer exec ProcAbortSample is 1001 Return is not affected Return parameter of ProcCallerSample is 0 IF next <SELECT * FROM TabExist> yield any row then INS statements are not rolled back p1 p2 ----------- ---------------- 1 ProcCallerSample 1 ProcAbortSample ---------------------------------------------------------------------------------------------
Conclusion:
SET XACT_ABORT ON is unable to force atomicity in case of errors caused by referencing non existing objects. So we need another weapon in order to shot these errors. There is no other way except using @@ERROR and RETURN parameter in combination and rollback transaction explicitly.
Example 2 - Using @@ERROR to handle "Non existing table" error
IF EXISTS(SELECT Name FROM Sysobjects where name ='ProcCallerSample')
DROP PROCEDURE ProcCallerSample
GO
CREATE PROCEDURE ProcCallerSample AS
DECLARE @Ret INT
BEGIN TRANSACTION
INSERT INTO TabExist Values('1','ProcCallerSample')
SET @Ret = 1001
EXEC @Ret = ProcAbortSample
IF @@Error <> 0
BEGIN
PRINT 'Error is caught, it follow Rollback'
ROLLBACK TRANSACTION
RETURN 1002
END
COMMIT TRANSACTION
GO
DECLARE @RetFinal INT
DELETE FROM TabExist
EXEC @RetFinal = ProcCallerSample
PRINT 'Return parameter of ProcCallerSample is ' + str(@RetFinal)
GO
PRINT 'IF next <SELECT * FROM TabExist> yield zero rows then INS statements are rolled back'
SELECT * FROM TabExist
---------------------------------------------------------------------------------------------
Result
Server: Msg 208, Level 16, State 1, Procedure ProcAbortSample, Line 3 Invalid object name 'TabNonExist'. Error is caught, it follow Rollback Return parameter of ProcCallerSample is 1002 IF next <SELECT * FROM TabExist> yield zero rows then INS statements are rolled back p1 p2 ----------- ---------------- (0 row(s) affected) ---------------------------------------------------------------------------------------------
This is just one case of complex error handling logic that has to be implemented in order to catch all possible errors. There is a different case of behavior depending on the type of error. Errors can cause statement termination, scope abortion, batch abortion or connection termination. There is no universal cure for all situations.
Finally, all that we have is @@Error and Return parameter. When we use them in combination, we can catch most errors from procedure of first level downward.
In next article Be Prepared – Part 2, I will discuss about error handling blocks depending on nested level of stored procedures.