handling transaction errors when objects don't exist

Question

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 not 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)

score 1 · Answer 1 · answered Jan 11 '11 at 21:25

1

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)

answered Jan 11 '11 at 21:25

2

yes, I could but this is not scalable. Imagine If I'm rolling out a test script that touches 50 objects, do I test for each one? It seems to me that try/catch should handle the error in the first place. – gr928x Jan 12 '11 at 13:11

Tony · Answer 2 · 2010-12-13T15:39:02.683

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.

Sometimes in dynamic environments, you don't know the precise schema; another process might be changing the database structure, so it's essential that normal try/catch exception handling should work as normal. — Vince Bowdren, Dec 12 '10 at 00:40
Fair point. Yes, the try/catch handling should work correctly but I wasn't defending MS; I just don't don't agree with this use of a database. — Tony, Dec 12 '10 at 23:35
Tony, This scenario comes up in our test and development environment where we have multiple developers working simultaneously on changes that they are making. Now this is a situation I have no control over, so I can't change it. However, since changes can occur to objects in test, I need to be able to handle errors generated from missing objects, and I thought the try/catch mechanism should be able to handle that. Since it doesn't I'm trying to figure out the best way to use transactions so that the changes I'm responsible for don't end up in some sort of inconsistent state. — gr928x, Dec 13 '10 at 14:08
@gr928x: I've updated my answer to include a way of handling this error. However, it appears your change control process is broken if you have multiple developers modifying DB objects on which your code depends. If you can't change the process try to get a personal copy of the DB to develop your code against and avoid the problem altogether. — Tony, Dec 13 '10 at 15:43

score 0 · Answer 3 · edited May 23 '17 at 10:28

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)

handling transaction errors when objects don't exist

3 Answers3