What is wrong with my Try Catch in T-SQL?

Question

I am using SQL Server 2008 and when I run this Statement in Management studio the Select statement in the Catch Block is executed as expected

BEGIN TRY
 INSERT INTO IDontExist(ProductID)
 VALUES(1)
END TRY
BEGIN CATCH
SELECT 'There was an error! ' + ERROR_MESSAGE()
END CATCH

However when I run this statement the statement in the Catch Block is never executed and instead the error is just displayed in the results tab

BEGIN TRY
  Select * from IDontExist
END TRY
BEGIN CATCH
  SELECT 'There was an error! ' + ERROR_MESSAGE()
END CATCH

They both return the same error number '208' 'Invalid Object Name: IDontExist' so why would one get handled and the other not?

Any feedback on our answers please? I'm quite curious about how you got on... — gbn, Feb 15 '11 at 21:39
Late question: what if you run DBCC FREEPROCCACHE to force recompilation — gbn, Jan 03 '12 at 13:47
The best answer I found to this was to use 'SET XACT_ABORT ON'. If this is turned on it will catch these. https://stackoverflow.com/questions/1150032/what-is-the-benefit-of-using-set-xact-abort-on-in-a-stored-procedure — Code Novice, Jun 15 '20 at 16:45

score 5 · Answer 1 · answered Dec 29 '10 at 17:38

5

This has bitten me in the past as well.

Not all errors generated inside the TRY block statements are passed into the CATCH block. Any errors with a severity of 10 or less are considered to be warnings and do not cause control to flow to the CATCH block. Also, any errors that break the database connection will not cause the CATCH block to be reached. There may be other situations as well.

answered Dec 29 '10 at 17:38

Randy Minder

47,200
49
204
358

4

How is this relevant to the OP's case? Error 208 has severity 16 and doesn't terminate the connection. – Martin Smith Dec 30 '10 at 01:27
..yes, and that's why it is sometimes nice solution to store errors in the table @var which should keep the error value(s) available in the CATCH block – Milan Apr 21 '15 at 23:36

gbn · Answer 2 · 2010-12-29T18:06:45.280

5

I don't get the CATCH block hit at all.

That's because the code won't compile, because the object doesn't exist, no plan is generated, so nothing runs to hit the CATCH block.

You can never hit this catch block so somethign is wrong with your testing/example. You can hit an outer catch block in a different scope (eg nested stored procs)

Edit: I'm using SQL Server 2005 SP3

It depends when deferred name resolution applies, related to statement level recompilation.

In my case, the whole batch fails both times and no statement level recompilation happens so no deferred name resolution
In OP's case, the batch compiles and runs but then has a statement level recompilation/deferred name resolution error in running code

I'm off to find some references about why it's different, given BOL doesn't say much, neither does Erland Sommarskog

edited Dec 29 '10 at 18:06

answered Dec 29 '10 at 17:44

gbn

422,506
82
585
676

I'm using SQL-2008 and in SSMS I see the catch block executed: There was an error! Ungültiger Objektname 'IDontExist'. – bernd_k Dec 29 '10 at 17:48
1

@bernd_k: I'm using SQL Server 2005. Behaviour has changed – gbn Dec 29 '10 at 17:54
1

I'm using SQL Server 2008 and get the same behaviour as you. `@@VERSION = Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) Sep 16 2010 19:43:16 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 (Build 7600: )` – Martin Smith Dec 29 '10 at 18:05
1

Tested on SQL 2008 Enterprise. Query did not compile. Invalid object name. – brian Dec 29 '10 at 18:07
I can reproduce this behaviour now if I create the table, cache the plans, then drop the table. – Martin Smith Jan 03 '12 at 14:15
@MartinSmith: Great minds and all that. I can't test my theory here (based on my DBCC comment above) but I was planning on doing that later when I get home. Cheers – gbn Jan 03 '12 at 14:17
1

@gbn - The difference is that the `INSERT` one gets auto parameterised. If I change the `SELECT` to `Select * from IDontExist WHERE ProductID = 1` then that behaves the same as the insert – Martin Smith Jan 03 '12 at 14:19
And actually I assume @bernd_k must have run it twice. It only isn't a catchable error on the first run when no plan exists. – Martin Smith Jan 03 '12 at 16:25

score 3 · Answer 3 · answered Dec 29 '10 at 17:41

Directly from http://msdn.microsoft.com/en-us/library/ms175976.aspx.

USE AdventureWorks2008R2;
GO

BEGIN TRY
    -- Table does not exist; object name resolution
    -- error not caught.
    SELECT * FROM NonexistentTable;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

The error is not caught and control passes out of the TRY…CATCH construct to the next higher level.

Running the SELECT statement inside a stored procedure will cause the error to occur at a level lower than the TRY block. The error will be handled by the TRY…CATCH construct.

Martin Smith · Answer 4 · 2012-01-03T16:20:19.043

This behaviour happens if you previously had a table IDontExist and compiled a plan for it that is still in the cache then drop the table.

It also happens if you run the individual statement twice even without the table ever existing. The first run raises an error that is not caught. The second run (after the first plan is cached) succeeds.

/*Clear Cache*/
DBCC FREEPROCCACHE

GO

BEGIN TRY
 INSERT INTO IDontExist(ProductID)
 VALUES(1)
END TRY
BEGIN CATCH
SELECT 'There was an error! ' + ERROR_MESSAGE()
END CATCH

GO
/*Plan now Cached*/

SELECT query_plan
FROM   sys.dm_exec_cached_plans cp
       OUTER APPLY sys.dm_exec_sql_text(plan_handle) t
       OUTER APPLY sys.dm_exec_query_plan(plan_handle) qp
WHERE  t.text LIKE '%IDontExist%'
OPTION (RECOMPILE)

GO

BEGIN TRY
 INSERT INTO IDontExist(ProductID)
 VALUES(1)
END TRY
BEGIN CATCH
SELECT 'There was an error! ' + ERROR_MESSAGE()
END CATCH

GO

The INSERT statement gets auto parameterised.

If you change your Select * from IDontExist statement to Select * from IDontExist WHERE ProductID = 1 this also becomes auto parameterised and they behave the same.

I'm not absolutely certain why the auto parameterisation makes a difference here. I think that it is explained by the below extract from BOL however.

The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct ... [those] that occur during statement-level recompilation ... If an error occurs during compilation or statement-level recompilation at a lower execution level (for example, when executing sp_executesql or a user-defined stored procedure) inside the TRY block, the error occurs at a lower level than the TRY…CATCH construct and will be handled by the associated CATCH block.

I presume the auto parametrization of that statement means that it gets recompiled at a lower execution level and is catchable.

score 0 · Answer 5 · answered Sep 06 '13 at 21:46

Now that we have all the explanations as to why this is happening. Let's see an actual solution to the problem.

First let's take the statements that @d-k-mulligan proposed above and turn them into stored procs.

IF OBJECT_ID('dbo.prcIDontExistINSERT', 'P') IS NOT NULL DROP PROCEDURE dbo.prcIDontExistINSERT
GO
CREATE PROCEDURE dbo.prcIDontExistINSERT 
AS
BEGIN TRY
 INSERT INTO IDontExist(ProductID)
 VALUES(1)
END TRY
BEGIN CATCH
  SELECT 'There was an error! ' + ERROR_MESSAGE()
END CATCH
GO

IF OBJECT_ID('dbo.prcIDontExistSELECT', 'P') IS NOT NULL DROP PROCEDURE dbo.prcIDontExistSELECT
GO
CREATE PROCEDURE dbo.prcIDontExistSELECT 
AS
BEGIN TRY
  SELECT * FROM IDontExist
END TRY
BEGIN CATCH
  SELECT 'There was an error! ' + ERROR_MESSAGE()
END CATCH
GO

If we run either of them we see the same error.

EXEC dbo.prcIDontExistINSERT
EXEC dbo.prcIDontExistSELECT

Msg 208, Level 16, State 1, Procedure prcIDontExistSELECT, Line 4
Invalid object name 'IDontExist'.

The solution now is to create error handling wrapper procs with the sole purpose of catching any error from the original procs above that are getting the object not found errors.

IF OBJECT_ID('dbo.prcIDontExistInsert_ERROR_HANDLER', 'P') IS NOT NULL DROP PROCEDURE dbo.prcIDontExistInsert_ERROR_HANDLER
GO
CREATE PROCEDURE dbo.prcIDontExistInsert_ERROR_HANDLER 
AS
BEGIN TRY
 EXEC dbo.prcIDontExistINSERT
END TRY
BEGIN CATCH
  SELECT 'There was an error! ' + ERROR_MESSAGE()
END CATCH
GO

IF OBJECT_ID('dbo.prcIDontExistSELECT_ERROR_HANDLER', 'P') IS NOT NULL DROP PROCEDURE dbo.prcIDontExistSELECT_ERROR_HANDLER
GO
CREATE PROCEDURE dbo.prcIDontExistSELECT_ERROR_HANDLER 
AS
BEGIN TRY
 EXEC dbo.prcIDontExistSELECT
END TRY
BEGIN CATCH
  SELECT 'There was an error! ' + ERROR_MESSAGE()
END CATCH
GO

Finally, let's run either of our error handling procs and see the message we expect.

EXEC dbo.prcIDontExistInsert_ERROR_HANDLER
EXEC dbo.prcIDontExistSELECT_ERROR_HANDLER

There was an error! Invalid object name 'IDontExist'.

NOTE: Kalman Toth did all the hard research work here: http://www.sqlusa.com/articles2008/trycatch/

score 0 · Answer 6 · answered Sep 09 '15 at 03:50

0

Workaround with dynamic sql. Maybe it will be helpful for someone.

begin try
    exec('
        insert into IDontExist(ProductID)
        values(1)
    ')
end try
begin catch
    select 'There was an error! ' + error_message()
end catch

answered Sep 09 '15 at 03:50

zatuliveter

16
2

What is wrong with my Try Catch in T-SQL?

6 Answers6

Linked