The Empty Catch Block: Handling Errors In SQL Server… Weirdly.

Don’t Know Why

Normally people handle errors to… handle errors. But I came across someone doing something sort of interesting recently.

Before we talk about that, let’s talk about the more normal way of capturing errors from T-SQL:

CREATE OR ALTER PROCEDURE
    dbo.error_muffler
(
    @i int
)
AS 
BEGIN
SET NOCOUNT, XACT_ABORT ON;

    BEGIN TRY
    
        SELECT 
            x = 1/@i;
    
    END TRY
    
    BEGIN CATCH
        /*Do some logging or something?*/
        THROW;
    END CATCH;

END;

So if we execute our procedure like this, it’ll throw a divide by zero error:

EXEC dbo.error_muffler 
    @i = 0;

Msg 8134, Level 16, State 1, Procedure dbo.error_muffler, Line 12 [Batch Start Line 33]

Divide by zero error encountered.

Well, good. That’s reasonable.

Empty Iterator

What I recently saw someone doing was using an empty catch block to suppress errors:

CREATE OR ALTER PROCEDURE
    dbo.error_muffler
(
    @i int
)
AS 
BEGIN
SET NOCOUNT, XACT_ABORT ON;

    BEGIN TRY
    
        SELECT 
            x = 1/@i;
    
    END TRY
    
    BEGIN CATCH
        /*Nothing here now*/
    END CATCH;

END;
GO

So if you execute the above procedure, all it returns is an empty result with no error message.

Kinda weird.

Like not having finger or toenails.

Trigger Happy

Of course (of course!) this doesn’t work for triggers by default, because XACT_ABORT is on by default..

CREATE TABLE 
    dbo.catch_errors
(
    id int NOT NULL
);
GO

CREATE OR ALTER TRIGGER
    dbo.bury_errors
ON
    dbo.catch_errors
AFTER INSERT
AS
BEGIN
    BEGIN TRY
        UPDATE c
            SET c.id = NULL
        FROM dbo.catch_errors AS c;
    END TRY
    BEGIN CATCH

    END CATCH;
END;
GO

If we try to insert a row here, we’ll get a really weird error message, unswallowed.

INSERT 
    dbo.catch_errors
(
    id
)
VALUES
(
    1
);

Womp:

Msg 3616, Level 16, State 1, Line 29

An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back.

If we were to SET XACT_ABORT OFF; in the trigger definition, it would work as expected.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.