Why isn't it recommended to use Goto for error handling ?

[Capture_it]

Many individuals have suggested utilizing the try/catch construct for error handling in SQL, but I'm uncertain about the distinctions between using it and not. I'm curious about the potential issues that may arise when handling errors without try/catch. (When using goto phrase)

BEGIN TRANSACTION

..... do something (insert/update)...

IF (ISNUMERIC(@NUMBER) = 1)

SET @MSG_CD = '200008' 

GOTO ERROR_C1

COMMIT TRANSACTION

ERROR_C1:

ROLLBACK TRANSCTION

[#2]

@Capture_it 

It's considered a poor flow control practice in every language that features it - not just SQL, since you can often jump completely arbitrarily around within a program (some have enforced scope limits to combat this; some don't) and inadvertently run code you had no intention of running.

Take your example above.

If there's no exception, it's going to process the COMMIT TRANSACTION immediately followed by the ROLLBACK TRANSACTION (which won't do anything but shouldn't have been executed at all if designed properly).

There should be a RETURN after the COMMIT TRANSACTION to avoid this unwanted scenario.

But if it was structed within a TRY..CATCH block, it simply wouldn't be possible for the COMMIT to succeed and be followed by the ROLLBACK.

So, the TRY..CATCH construct inherently promotes better code design where GOTO detracts from it.

That is not to say it cannot or must not be used, but that is why it's not recommended.

Cheers,

Lain