Bad practice using SQL Server GOTO to handle errors?

Question

I read about error handling in SQL Server in this article , and they suggest using SQL Server GOTO in certain situations to roll back a 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 almost 10 years ago, and I heard that it’s usually a bad idea to use GOTO. Is the ok method above error handling in SQL Server? If not, can anyone suggest a better alternative?

Answer 1

You must use 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 

Answer 2

You must enable 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