handling transaction errors when objects don't exist

I found and article in the MSDN Lbrary explaining that try/catch does not handle errors thrown when an object cannot be found. SO, even though I wrap a transaction in a try/catch, the rollback phrase will not execute:

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's the recommended way to handle errors thrown when an object does no开发者_StackOverflow社区t exists, especially when there is a transaction involved. Should I tack this bit of code in place 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)

You can test for the existence of an object with OBJECT_ID():

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

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

The fundamental building block of a database is a table. Not knowing what is in your schema is essentially trying to use SQL as a dynamic language, which it is not.

I would rethink your design; without knowing more about the tables in your database and its intended use it's hard for others to help in this regard. Add some more information to your question please.

EDIT I've had a read of BOL and the recommended way to handle errors when an object does not exist is as follows:

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

I tested this out using the following procedure

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

Then called it within a 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

Resulting in the following output:

Error Number before: 0

Error Number in catch: 208

It's not a perfect solution as you will have to create procedures (or use dynamic SQL) for all of your table access, but it's the method recommend by MS.

Now, you've hit on an interesting issue (well, to me anyway). You should not start a transaction because the batch won't compile. However, it can compile and then statement level recompilation fails later.

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

However, in either case you can use SET XACT_ABORT ON. This adds predictability because one effect is to automatically roll back any transaction. It also suppress 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)