try catch for non existant table

Question

Hello,

WHen i try a SELECT on a non-existing table it terminates saying the table doesn't exist and doesn't go to catch statements but when I do a truncate on nonexistant table it goes to catch statements. what could be the reason?

Thanks,
ACDBA

Answer 1

Unfortunately, we will have to discard Viorel's suggestion is that TRUNCATE TABLE is DDL. as the explanation. Look at this:

SET XACT_ABORT OFF
BEGIN TRANSACTION
BEGIN TRY
   PRINT 'Prints'
   ALTER TABLE nosuchtable ALTER COLUMN col a 
END TRY
BEGIN CATCH
   SELECT xact_state() AS xact_state, error_message() AS errmsg
END CATCH
PRINT 'Does not print'
go
SELECT @@trancount
IF @@trancount > 0 ROLLBACK TRANSACTION

The output is:

Prints
Msg 4902, Level 16, State 1, Line 128
Cannot find the object "nosuchtable" because it does not exist or you do not have permissions.

-----------
1

Which is the same output as we get when have DELETE nosuchtable.

So I will go back to my original answer: because error handling in SQL Server is a big mess.

Answer 2

The behaviour is well documented below.

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-ver15#errors-unaffected-by-a-trycatch-construct

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:

• Compile errors, such as syntax errors, that prevent a batch from running.
• Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.
• Object name resolution errors

But if you use the statement inside a stored procedure, it will be caught.

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.

-- Create a stored procedure that will cause an     
-- object resolution error.    
CREATE PROCEDURE usp_ExampleProc    
AS    
    SELECT * FROM NonexistentTable;    
GO    
    
BEGIN TRY    
    EXECUTE usp_ExampleProc;    
END TRY    
BEGIN CATCH    
    SELECT     
        ERROR_NUMBER() AS ErrorNumber    
        ,ERROR_MESSAGE() AS ErrorMessage;    
END CATCH;    

Hope this helps

Answer 3

Thank you..I did try it and it worked...But when we try the same in statements why we are getting two different actions for table absence?

Answer 4

I just re-read this excellent article sommarskog.se/error_handling/Part3.html#clientcode (actually, I started from part 1 and then at the end just jumped to the part 3). I suggest you to read it.

Answer 5

Hi @ACDBA
Disclaimer : Just my guessing, not guaranteed to be correct.
Please have a check on the difference between these two error messages:
For SELECT * FROM nonexistant , the error message is Invalid object name 'nonexistant'
For TRUNCATE TABLE nonexistant, the error message is Cannot find the object "nonexistant" because it does not exist or you do not have permissions

From your side , you clearly know that 'nonexistant' does not exist, and it should be 'Object name resolution errors'.
However, from SQLSever side, it can only tell it is 'Object name resolution errors' in SELECT statement which is not caught by the TRY...CATCH construct.
When coming to TRUNCATE TABLE nonexistant it can't tell if the table doesn't exist or you do not have permissions,therefore the error of TRUNCATE can be caught by the TRY...CATCH construct.

Best regards,
LiHong