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.
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 proc, all it returns is an empty result with no error message.
Like not having finger or toenails.
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 );
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!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on 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.