Some T-SQL code is written under the assumption that either
a TRY block successfully completes or a CATCH block is invoked. Most likely,
this is the case. However, there is a third, although rare, possibility – the
TRY block may fail, and the CATCH one is bypassed. Let me provide some
examples. I do not intend to provide a comprehensive list of all such cases, I
only want to demonstrate that sometimes CATCH blocks are bypassed. Also I would
like to emphasize that in almost all the cases CATCH blocks do catch errors,
and the exceptions, if any, are quite rare.
KILL command and
timeout (aka attention) both stop execution without invoking CATCH blocks.
As a result of a KILL or an attention, the execution stops
immediately. You can run the following script, cancel the query or kill it from
another tab, and see for yourself:
SELECT @@SPID;
GO
BEGIN TRY
PRINT 'Before WAITFOR';
WAITFOR DELAY
'00:35:00';
PRINT 'After WAITFOR';
END TRY
BEGIN CATCH
SELECT 'Beginning CATCH block';
SELECT
ERROR_NUMBER(), ERROR_MESSAGE();
END CATCH
PRINT 'At the end of the same batch';
GO
PRINT 'Next batch';
You can also invoke it from ADO.Net, and you can also
decrease CommandTimeout, so that the timeout occurs faster.
As you have seen, you can only catch such errors on the
client.
Note: timeout and
lock timeout are different. Lock timeout errors are caught by CATCH blocks.
Some compile
errors cancel batch execution.
See for yourself:
BEGIN TRY
PRINT 'Beginning TRY';
BEGIN TRAN
INSERT data.SomeData(ID, AnotherID) VALUES(-1, -2);
-- make sure #t does not exists for your connection
DELETE FROM #t;
COMMIT;
PRINT 'Finishing TRY';
END TRY
BEGIN CATCH
SELECT 'Beginning CATCH block';
SELECT
ERROR_NUMBER(), ERROR_MESSAGE();
ROLLBACK;
END CATCH
PRINT 'At the end of the same batch';
GO
PRINT 'Next batch';
Beginning TRY
(1 row(s) affected)
Msg 208, Level 16, State 0,
Line 6
Invalid object name '#t'.
Next batch
This problem is more likely to happen if you work with
temporary tables. However, because at
the time of this writing we have deferred name resolution, you can get this
problem with a permanent object too. Surprisingly enough, if you wrap this
batch in a stored procedure, and invoke your procedure from another TRY block,
the second TRY block does catch the error. You can create the procedure and see
for yourself:
CREATE PROCEDURE dbo.TestProc
AS
BEGIN TRY
PRINT 'Beginning TRY';
DELETE FROM #t;
--INSERT
data.SomeData(j) SELECT 1;
PRINT 'Finishing TRY';
END TRY
BEGIN CATCH
SELECT 'Beginning CATCH block';
SELECT
ERROR_NUMBER(), ERROR_MESSAGE();
END CATCH
PRINT 'At the end of the same batch';
GO
BEGIN TRY
PRINT 'Beginning TRY';
EXEC dbo.TestProc;
PRINT 'Finishing TRY';
END TRY
BEGIN CATCH
SELECT 'Beginning CATCH block';
SELECT
ERROR_NUMBER(), ERROR_MESSAGE();
END CATCH
PRINT 'At the end of the same batch';
In my opinion it would be preferable not to start executing
a batch if there are compilation errors, and in most cases exactly this is happening:
BEGIN TRY
PRINT 'Beginning TRY';
DECLARE @i INT;
INSERT data.SomeData(j) SELECT 1;
PRINT 'Finishing TRY';
END TRY
BEGIN CATCH
SELECT 'Beginning CATCH block';
SELECT
ERROR_NUMBER(), ERROR_MESSAGE();
END CATCH
PRINT 'At the end of the same batch';
GO
PRINT 'Next batch';
Msg 207, Level 16, State 1,
Line 4
Invalid column name 'j'.
Next batch
If you are still reading this, also make sure you have read
Erland Sommarskog’s articles on error handling:
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
and STRICT_CHECKS:
http://www.sommarskog.se/strict_checks.html
This post continues my series on defensive database programming. My next post:
Without ORDER BY, there is no default sort order.