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?

Answer 1

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