Bad practice to use SQL Server's GOTO for error handling?

Question

I was reading about error handling in SQL Server in this article, and they suggest using SQL Server's GOTO in certain situations to roll back the transaction. Example:

BEGIN TRAN
    UPDATE Authors
    SET Phone = '415 354-9866'
    WHERE au_id = '724-80-9391'

    SELECT @intErrorCode = @@ERROR
    IF (@intErrorCode <> 0) GOTO PROBLEM

    UPDATE Publishers
    SET city = 'Calcutta', country = 'India'
    WHERE pub_id = '9999'

    SELECT @intErrorCode = @@ERROR
    IF (@intErrorCode <> 0) GOTO PROBLEM
COMMIT TRAN

PROBLEM:
IF (@intErrorCode <> 0) BEGIN
PRINT 'Unexpected error occurred!'
    ROLLBACK TRAN
END

This article was written nearly 10 years ago and I've heard that it's usually a bad idea to use GOTO. Is the above an ok method for error handling in SQL Server? If not, can anyone suggest a better alternative?

Did you check this [answer](http://stackoverflow.com/a/11153012/1451723)? — Pankaj, Jun 24 '12 at 05:01

SliverNinja - MSFT · Accepted Answer · 2012-06-22T15:35:57.123

20

You should be using Try/Catch in SQL 2005+

BEGIN TRY
    BEGIN TRAN

    UPDATE Authors
    SET Phone = '415 354-9866'
    WHERE au_id = '724-80-9391'

    UPDATE Publishers
    SET city = 'Calcutta', country = 'India'
    WHERE pub_id = '9999'

    COMMIT TRAN        
END TRY
BEGIN CATCH
    PRINT 'Unexpected error occurred!'
    IF XACT_STATE() <> 0
        ROLLBACK TRAN    
END CATCH

edited Jun 22 '12 at 15:35

answered Jun 21 '12 at 15:45

SliverNinja - MSFT

31,051
11
110
173

In catch, print `ERROR_MESSAGE()` – Bernardo Dal Corno Nov 24 '21 at 16:13
if XACT_STATE is 1, we can still commit the transaction if wanted. If -1, always rollback – Bernardo Dal Corno Nov 24 '21 at 16:19

Pankaj · Answer 2 · 2012-06-24T05:00:17.450

8

You must incorporate SET XACT_ABORT ON in Exception handling

Begin Try
SET XACT_ABORT ON

BEGIN TRAN
    UPDATE Authors
    SET Phone = '415 354-9866'
    WHERE au_id = '724-80-9391'

    UPDATE Publishers
    SET city = 'Calcutta', country = 'India'
    WHERE pub_id = '9999'
COMMIT TRAN

End Try

Begin Catch
    Rollback Tran
End Catch

edited Jun 24 '12 at 05:00

answered Jun 22 '12 at 08:52

Pankaj

9,749
32
139
283

Bad practice to use SQL Server's GOTO for error handling?

2 Answers2