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

score 14 · Accepted Answer · edited May 08 '19 at 12:11

14

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.

edited May 08 '19 at 12:11

mzamora

161
2
10

answered Jul 10 '09 at 19:34

A-K

16,804
8
54
74

5

The linked article by Erland Sommarskog is for SQL Server 2000. For his article on SQL Server 2005 see here: http://www.sommarskog.se/error_handling_2005.html – Richard Marskell - Drackir Nov 25 '14 at 19:17
@RichardMarskell-Drackir is there one for SQL Server 2008? I mean the link says 2005 and later, but.. – Apostrofix Mar 31 '15 at 13:00
1

@Apostrofix - The changes from 2005+ should be relatively minimal compared to the changes made between 2000 and 2005, so I'd imagine the 2005 article still applies. – Richard Marskell - Drackir Apr 08 '15 at 02:38
Linked article by Erland Sommarskog has been moved again and is relocated and revised [here](http://www.sommarskog.se/error_handling/Part1.html) – EAmez May 10 '19 at 11:49

gbn · Answer 2 · 2009-07-10T19:41:38.137

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

SQLMenace · Answer 3 · 2009-07-10T19:45:01.013

4

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.

edited Jul 10 '09 at 19:45

answered Jul 10 '09 at 19:35

SQLMenace

132,095
25
206
225

1

why not have the begin/commit inside the try? – gbn Jul 10 '09 at 19:40
same result, you need to check for XACT_STATE to be sure because of still non trapable errors and doomed state – SQLMenace Jul 10 '09 at 19:45
True, but the commit inside would transfer to the catch block and would never run. And you'd expect a rollback in the catch block too. Also, OP has SET XACT_ABORT ON which has an automatic rollback. – gbn Jul 10 '09 at 19:51
I understand I was just trying to make a point that catch doesn't catch everything..I always use SET XACT_ABORT ON in my procs – SQLMenace Jul 10 '09 at 19:55
2

I use it too, but I can't see how you are demonstrating when the commit is outside the try/catch – gbn Jul 10 '09 at 19:58

score 0 · Answer 4 · answered Jul 10 '09 at 19:33

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.

No, you cannot catch error with a severity higher than 20. Also, you cannot catch warnings. — DCNYAM, Jul 10 '09 at 19:35

score 0 · Answer 5 · answered Jul 10 '09 at 19:33

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.

score 0 · Answer 6 · answered Jul 10 '09 at 20:57

0

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.

answered Jul 10 '09 at 20:57

dance2die

35,807
39
131
194

You mean checking for `@@ERROR` inside a `TRY...CATCH` is not worthwhile. Your original statement wasn't clear. – Suncat2000 Sep 26 '17 at 13:50

@@ERROR and/or TRY - CATCH

6 Answers6

Linked