Issues with T-SQL TRY CATCH?

问题:

We are currently on SQL 2005 at work and I am migrating an old Foxpro system to new web application backed by SQL Server. I am using TRY CATCH in T-SQL for transaction processing and it seems to be working very well. One of the other programmers at work was worried about this as he said he had heard of issues where the catch phrase did not always catch the error. I have beat the sproc to death and cannot get it to fail (miss a catch) and the only issues I have found searching around the net is that it will not return the correct error number for error numbers < 5000. Has anyone experienced any other issues with TRY CATCH in T-SQL - especially if it misses a catch? Thanks any input you may wish to provide.

回答1:

TRY ... CATCH doesn't catch every possible error but the ones not caught are well documented in BOL Errors Unaffected by a TRY…CATCH Construct

TRY…CATCH constructs do not trap the following conditions:

• Warnings or informational messages that have a severity of 10 or lower.
• Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY…CATCH will handle the error.
• Attentions, such as client-interrupt requests or broken client connections.
• When the session is ended by a system administrator by using the KILL statement.

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.

These errors are returned to the level that ran the batch, stored procedure, or trigger.

回答2:

There was one case in my experience when TRY...CATCH block didn't catch the error. There was error connected with collation: Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_CI_AI" in the equal to operation. Maybe this error correspond one of the error type documented in BOL.

Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

回答3:

TRY ... CATCH will fail to catch an error if you pass a "bad" search term to CONTAINSTABLE

For example:

DECLARE @WordList VARCHAR(800)
SET @WordList = 'crap"s'
CON

TAINSTABLE(table, *, @WordList)

The CONTAINSTABLE will give you a "syntax error", and any surrounding TRY ... CATCH does not catch this.

This is particularly nasty because the error is caused by data, not by a "real" syntax error in your code.

回答4:

I'm working in SQL Server 2008. I built a big sql statement that had a try/catch. I tested it by renaming a table (in dev). The statement blew up and didn't catch the error. Try/catch in SQL Server is weak, but better than nothing. Here's a piece of my code. I can't put any more in because of my company's restrictions.

    COMMIT TRAN T1;
END TRY
BEGIN CATCH
    -- Save the error.
    SET @ErrorNumber = ERROR_NUMBER();
    SET @ErrorMessage = ERROR_MESSAGE();
    SET @ErrorLine = ERROR_LINE();
    -- Put GSR.dbo.BlahBlahTable back the way it was.
    ROLLBACK TRAN T1;   
END CATCH
-- Output a possible error message. Knowing what line the error happened at really helps with debugging.
SELECT @ErrorNumber as ErrorNumber,@ErrorMessage as ErrorMessage,@ErrorLine AS LineNumber;

回答5:

I have never hit a situation where TRY...CATCH... failed. Neiteher, probably, have many of the people who read this question. This, alas, only means that if there is such a SQL bug, then we haven't seen it. The thing is, that's a pretty big "if". Believe it or not, Microsoft does put some effort into making their core software products pretty solid, and TRY...CATCH... is hardly a new concept. A quick example: In SQL 2005, I encountered a solid, demonstrable, and replicable bug while working out then-new table partitioning--which bug that had already been fixed by a patch. And TRY...CATCH... gets used a bit more frequently than table partitioning.

I'd say the burden of proof falls on your co-worker. If he "heard it somewhere", then he should try and back it up with some kind of evidence. The internet is full of proof for the old saying "just because everyone says its so, doesn't mean they're right".