use of GOTO & Labels in SQL inside SQL Transactions

Question

I have a long running script that I want to wrap in transactions which could have up to 100+ transactions in it.

I would like to implement some error handling and re-use of code. I was thinking of using a label and GOTO statements.

BEGIN TRY
BEGIN TRANSACTION abc
    --DO SOMETHING
    COMMIT TRANSACTION abc;
END TRY
BEGIN CATCH
    GOTO ERROR_OUT
END CATCH

ERROR_OUT:  
       SELECT ERROR_MESSAGE() [ErrorMessage];  
       ROLLBACK;

Is this something that should be done while using many transactions?

In my simple tests where I forced an error I noticed that the ERROR_MESSGE() SELECT statement did not return results.

Is there a way to get that to select statement to return results when using a label?

I would run away from using GOTO as fast as possible. It creates code that is incredibly painful to maintain. Especially with 100+ such transaction think about spaghetti like this code will be. I would argue that your script is already way too long for reasonable maintenance. — Sean Lange, Sep 16 '16 at 17:02
@SeanLange Agreed however, this is a one time script and I really want decent error messages on the parts that fail. — webdad3, Sep 16 '16 at 17:20
Combine checking for @@ERROR and Throwing Custom Exceptions. You can get a nice error out and rollback your tx without using a label. — Ross Bush, Sep 16 '16 at 17:43
have you tried assigning it to a variable inside your catch? — S3S, Sep 16 '16 at 17:44

score 4 · Answer 1 · answered Sep 16 '16 at 17:44

Declare @Table table (Num float,Den float,Pct float)
Insert into @Table values (25,100,0),(50,0,0)

BEGIN TRY
BEGIN TRANSACTION abc
    -- Force a Divide by Zero on 2nd record
    Update @Table Set Pct=Num/Den
    COMMIT TRANSACTION abc;
    Select * from @Table
    GOTO CLEAN_OUT
END TRY
BEGIN CATCH
    Declare @Error varchar(max)=Error_Message()
    GOTO ERROR_OUT
END CATCH

ERROR_OUT: 
    ROLLBACK;
    Select ErrorMessage=@Error 
    Select * From @Table

CLEAN_OUT:
Go

Returns

ErrorMessage
Divide by zero error encountered.

Num Den Pct
25  100 0
50  0   0

And then try it with (50,125,0)

Thanks, I tested this out and it works. I will be implementing part of your answer to my code in the future. — webdad3, Sep 16 '16 at 18:33

score 3 · Accepted Answer · edited Jan 05 '22 at 10:07

In general, programmers are discouraged from using goto. The reason is that it is a control-flow primitive, and alternatives exist for many commonly used cases: if/then/else, while, and so on.

However, there are circumstances where goto is quite useful. And error handling is one of them. If you google "goto error handling", it is not hard to find explanations such as this, or this (although for C, I think the reasoning is very similar).

I have often used goto in SQL Server stored procedures for exactly this purpose. Although in my case, the goal was to achieve proper auditing and error processing when leaving the stored procedure.

Your use-case would seem to be a fine example where goto is a very reasonable way to code the block. I do find the "hundreds of transactions" part more questionable. Personally, I like to be explicit about when and where transactions are processed, so I would be more likely to write:

. . .
begin catch:
    rollback ;
    goto error_out;
end;

That is, to explicitly rollback the transaction "next to" where the transaction begins, rather than doing that in some far-away code block.

Thanks for the Answer. I'm not sure how else I could do my long running script as I will have to hand this off to someone else in the production environment. But that might be another question. Thanks again! — webdad3, Sep 16 '16 at 18:32

use of GOTO & Labels in SQL inside SQL Transactions

2 Answers2