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.