@@ERROR and/or TRY - CATCH

Question

Will Try-Catch capture all errors that @@ERROR can? In the following code fragment, is it worthwhile to check for @@ERROR? Will RETURN 1111 ever occur?

SET XACT_ABORT ON
BEGIN TRANSACTION

BEGIN TRY
    --do sql command here  <<<<<<<<<<<

    SELECT @Error=@@ERROR
    IF @Error!=0
    BEGIN
        IF XACT_STATE()!=0
        BEGIN
            ROLLBACK TRANSACTION
        END
        RETURN 1111
    END

END TRY
BEGIN CATCH

    IF XACT_STATE()!=0
    BEGIN
        ROLLBACK TRANSACTION
    END
    RETURN 2222

END CATCH

IF XACT_STATE()=1
BEGIN
    COMMIT
END

RETURN 0

Answer 1

TRY/CATCH traps more. It's hugely and amazingly better.

DECLARE @foo int

SET @foo = 'bob' --batch aborting pre-SQL 2005
SELECT @@ERROR
GO
SELECT @@ERROR  --detects 245. But not much use, really if the batch was a stored proc
GO


DECLARE @foo int
BEGIN TRY
    SET @foo = 'bob'
    SELECT @@ERROR
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE(), ERROR_NUMBER()
END CATCH
GO

Using TRY/CATCH in triggers also works. Trigger rollbacks used to be batch aborting too: no longer if TRY/CATCH is used in the trigger too.

Your example would be better if the BEGIN/ROLLBACK/COMMIT is inside, not outside, the construct

Answer 2

I don't believe control will ever reach the RETURN statement-- once you're in a TRY block, any error raised will transfer control to the CATCH block. However, there are some very serious errors that can cause the batch or even the connection itself to abort (Erland Sommarskog has written on the topic of errors in SQL Server here and here-- unfortunately, he hasn't updated them to include TRY...CATCH). I'm not sure if you can CATCH those kind of error, but then, @@ERROR is no good either.

Answer 3

It has been my experience that, as per Books Online, TRY...CATCH blocks will trap all events that would generate errors (and, thus, set @@ERROR to a non-zero value). I can think of no circumstances where this would not apply. So no, the return value would never be set to 1111, and it would not be worthwhile to include that @@Error check.

However, error handling can be very critical, and I'd hedge my bets for fringe situations such as DTC, linked servers, notification or brokerage services, and other SQL feature that I've had very little experience with. If you can, test your more bizarre situations to see what will actually happen.

Answer 4

The following article is a must read by Erland Sommarskog, SQL Server MVP: Implementing Error Handling with Stored Procedures

Also note that Your TRY block may fail, and your CATCH block may be bypassed

One more thing: Stored procedures using old-style error handling and savepoints may not work as intended when they are used together with TRY … CATCH blocks.Avoid mixing old and new styles of error handling.

Answer 5

Try Catch will not trap everything

here is some code to demonstrate that

    BEGIN TRY
      BEGIN TRANSACTION TranA
     DECLARE  @cond INT;
     SET @cond =  'A';
    END TRY
    BEGIN CATCH
     PRINT 'a'
    END CATCH;
    COMMIT TRAN TranA

Server: Msg 3930, Level 16, State 1, Line 9 The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. Server: Msg 3998, Level 16, State 1, Line 1 Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

Answer 6

The whole point of "Try..Catch" is so that you don't have to check for @@ERROR for every statement.

So it's not worthwhile.