@@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

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 2

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