Handling transaction errors when objects do not exist

Question

I also found an article on MSDN Lbrary explaining that try / catch does not handle errors that occur when an object cannot be found. SO, even if I complete the transaction in try / catch, the rollback phrase will not be executed:

BEGIN TRY BEGIN TRANSACTION SELECT 1 FROM dbo.TableDoesNotExists PRINT ' Should not see this' COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION SELECT ERROR_MESSAGE() END CATCH --PRINT 'Error Number before go: ' + CAST(@@Error AS VARCHAR) go PRINT 'Error Count After go: ' + CAST(@@Error AS VARCHAR) PRINT 'Transaction Count ' + CAST(@@TRANCOUNT AS VARCHAR) 

What is the recommended way to handle errors that occur when there is no object, especially when there is a transaction. Should I use this bit of code instead of the last two print statements:

 IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'Rolling back txn' ROLLBACK TRANSACTION END go PRINT 'Transaction Count again: ' + CAST(@@TRANCOUNT AS VARCHAR) 

Answer 1

You can check for an object with OBJECT_ID ():

 IF OBJECT_ID('MyTable') IS NULL RAISERROR('Could not find MyTable.', 18, 0) 

Answer 2

Why are you trying to retrieve data from a table that does not exist?

The main building block of the database is the table. Not knowing what is in your schema, basically trying to use SQL as a dynamic language, but it is not.

I would review your design; without knowing more about the tables in your database and its intention to use others to do this to help in this regard. Add more information to your question.

EDIT I read BOL, and the recommended way to handle errors when the object does not exist is as follows:

You can use TRY ... CATCH to handle errors that occur during compilation or recompile at the level of execution of the code that generates errors into a separate batch in the TRY block. For example, you do this by placing code in a stored procedure or executing dynamic Transact-SQL using sp_executesql. This allows TRY ... CATCH to catch the error at a higher level of execution than the error.

I checked this using the following procedure

 CREATE PROCEDURE [dbo].[BrokenProcedure] AS BEGIN SET NOCOUNT ON; SELECT * FROM MissingTable END GO 

Then called in the TRY..CATCH block:

 BEGIN TRY PRINT 'Error Number before: ' + CAST(@@Error AS VARCHAR) EXECUTE [dbo].[BrokenProcedure] PRINT ' Should not see this' END TRY BEGIN CATCH PRINT 'Error Number in catch: ' + CAST(@@Error AS VARCHAR) END CATCH 

The result of the following output:

Error number to: 0

Catch Error Number: 208

This is not an ideal solution, since you have to create procedures (or use dynamic SQL) for all access to the table, but this method is recommended by MS.

Answer 3

Now you are in an interesting problem (well, anyway). You should not start a transaction because the package will not compile. However, it can compile and then recompile the instruction level later.

See this question. What happened to my attempt to Catch in T-SQL?

However, in any case, you can use SET XACT_ABORT ON . This adds predictability because one effect is to automatically roll back any transaction. It also suppresses error 266. See this SO question too

 SET XACT_ABORT ON BEGIN TRY BEGIN TRANSACTION SELECT 1 FROM dbo.TableDoesNotExists PRINT ' Should not see this' COMMIT TRANSACTION END TRY BEGIN CATCH -- not needed, but looks weird without a rollback. -- you could forget SET XACT_ABORT ON -- Use XACT_STATE avoid double rollback errors IF XACT_STATE() <> 0 ROLLBACK TRANSACTION SELECT ERROR_MESSAGE() END CATCH go --note, this can't be guaranteed to give anything PRINT 'Error Count After go: ' + CAST(@@Error AS VARCHAR) PRINT 'Transaction Count ' + CAST(@@TRANCOUNT AS VARCHAR)