Purpose of Try/Catch without Transactions

Question

I have come across the following code (SQL Server 2016, 2008-compatible database):

CREATE PROCEDURE myProc
AS BEGIN
    DECLARE @ERRORMESSAGE As VARCHAR(1000)

    BEGIN TRY   
        DELETE FROM Foo -- (statement #1)
        DELETE FROM Bar -- (statement #2)
    END TRY
    BEGIN CATCH
        SET @ERRORMESSAGE = ERROR_MESSAGE()
        RaisError(@ERRORMESSAGE,16,1) 
    END CATCH   
END

What is the purpose of the try/catch block?

For the sake of discussion, lets assume there are no triggers called and the client code that executes this procedure does not wrap the call in a transaction.

I had to fix a bug in code similar to this where statement #2 raised an error. Looking at the data it appeared statement #1 completed successfully and statement #2 did not apply (obviously).

One thing this does is not give a clear line number when trying to debug the crash, but is there anything legitimate this does?

Answer 1

I'm not sure that this specific TRY ... CATCH does anything particularly useful. It loses many of the details that would have existed where the actual error occurred (error number, severity, state, procedure/trigger, and line number).

The only obvious thing is does do that could be useful is force the error severity to 16; TRY ... CATCH catches errors with a severity of 10 or higher, as long as they don't shut down the connection. That may be deliberate (though I would tend to doubt it).

Answer 2

IMHO Using a TRY/CATCH block you don't need to check every single operation.

DELETE FROM Foo;
IF @@ERROR <> 0
BEGIN
    RAISERROR('Error deleting table Foo', 16, 1);
    RETURN -1;
END

OR

DELETE FROM Bar;
IF @@ERROR <> 0
BEGIN
    SET @ErrMsg = 'Error';
    GOTO ERROR;
END

Answer 3

You can use TRY / CATCH to implement business logic. For example, I've seen people not know about UPSERT/MERGE do exactly this:

1. TRY an insert into table
2. CATCH duplicate PK error
3. update row with that PK with same data that was going to be inserted

In your example, maybe the system isn't supposed to allow certain types of records to be deleted. If the application tries that, it logs an error.