The TRY…CATCH error control method has been introduced in SQL Server 2005 following similar exception handling construct in C++ and C# languages. A TRY…CATCH construct catches all execution errors with severity greater than 10 that will not result in the termination of the database connection. As such, it is very useful in verification of the database bound information. Error handling tasks can be handled in stored procedures which required client logic formerly.
But not every error is caught, even though SQL database developers may prefer that feature very much. Wrong object name errors are not caught, the control flow breaks altogether. Even Query Editor parser is not catching these errors. Syntax errors are also outside the domain of errors for TRY…CATCH. Syntax errors, on the other hand, are caught by Management Studio Query Editor parser.
There is an interesting technique for catching all errors: nest the TRY-CATCH stored procedure inside another TRY-CATCH stored procedure. The outer sproc will catch the uncaught errors by the inner sproc.
The following examples with results demonstrate what happens for different exception scenarios.
use tempdb;
-- Identity insert attempt flows to CATCH
begin try
insert into AdventureWorks2008.Production.Product (ProductID)
select 800
print 'Try passed OK'
print 'Try passed OK'
end try
begin catch
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
end catch
/* CATCH
ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage
544 16 1 NULL 2 Cannot insert explicit value for identity column in table 'Product' when IDENTITY_INSERT is set to OFF.
*/
*/
-- The following examples don't flow to CATCH
-- In some cases the ERROR is a compile error, other cases execution error
begin try
select HighestTicket=dateadd(UnitPrice)
from AdventureWorks2008.Sales.SalesOrderDetail
print 'Try passed OK'
end try
begin catch SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
end catch
/* NO CATCH ERROR
/* NO CATCH ERROR
Msg 174, Level 15, State 1, Line 2
The dateadd function requires 3 argument(s).
*/
begin try
select HighestTicket=maxi(UnitPrice)
from AdventureWorks2008.Sales.SalesOrderDetail
print 'Try passed OK'
end try
begin catch
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
end catch
/* NO CATCH ERROR
Msg 195, Level 15, State 10, Line 2
'maxi' is not a recognized built-in function name.
*/
begin try
select HighestTicket=max(UnitPrice)
from AdventureWorks2008.Sales.SalesOrderDetailOMEGA
print 'Try passed OK'
end try
begin catch
SELECT
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
end catch
/* NO CATCH ERROR Message
Msg 208, Level 16, State 1, Line 2
Invalid object name 'AdventureWorks2008.Sales.SalesOrderDetailOMEGA'.
*/
begin try
insert AdventureWorks2008.Production.Product (Name)
insert AdventureWorks2008.Production.Product (Name)
select values 'Rocky Mountain Bike'
print 'UPDATE succeeded'
end try
begin catch
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
end catch
/*
NO CATCH ERROR Message
Msg 156, Level 15, State 1, Line 3
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'values'.
*/
------------
Following is an interesting technique for catching all errors: nest the TRY-CATCH stored procedure ( sprocHighestTicketOrder) inside another TRY-CATCH stored procedure (sprocMAINHighestTicketOrder). The outer sproc will catch the uncaught errors by the inner sproc. We can test for where the error occured by looking at the ERROR_PROCEDURE() function (ErrorProcedure) return.
-- MSSQL script passes parse, however, it throws an execution error
-- T-SQL TRY - CATCH not catching
BEGIN TRY
DECLARE @HighestTicket money
SELECT @HighestTicket = MAX(UnitPrice)
FROM AdventureWorks2008.Sales.SalesOrderDetailx
PRINT 'Try passed OK'
END TRY
BEGIN CATCH
SELECT WhereAmI = 'scriptHighestTicketOrder',
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
GO
/* Msg 208, Level 16, State 1, Line 5
Invalid object name 'AdventureWorks2008.Sales.SalesOrderDetailx'.
*/
-- T-SQL erroneous stored procedure compiles OK
-- Internal TRY-CATCH will not catch
CREATE PROC sprocHighestTicketOrder @HighestTicket money OUTPUT
AS
BEGIN
BEGIN TRY
BEGIN TRY
SELECT @HighestTicket = MAX(UnitPrice)
FROM AdventureWorks2008.Sales.SalesOrderDetailx
PRINT 'Try passed OK'
END TRY
BEGIN CATCH
SELECT WhereAmI = 'sprocHighestTicketOrder',
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END
GO
-- T-SQL stored procedure without error
-- Outer sproc will catch error not caught in inner sproc
CREATE PROC sprocMAINHighestTicketOrder
AS
BEGIN
BEGIN TRY
DECLARE @HighestTicket money
EXEC sprocHighestTicketOrder @HighestTicket OUTPUT
PRINT @HighestTicket
PRINT @HighestTicket
PRINT 'Try passed OK'
END TRY
BEGIN CATCH
BEGIN CATCH
SELECT WhereAmI = 'sprocMAINHighestTicketOrder',
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END
GO
EXEC sprocMAINHighestTicketOrder
GO
/* Results
WhereAmI ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessagesprocMAINHighestTicketOrder 208 16 1 sprocHighestTicketOrder 6 Invalid object name 'AdventureWorks2008.Sales.SalesOrderDetailx'.
------------ */
The execution stops immediately, without executing the CATCH block. Listing 6 demonstrates that the connection is still in the middle of an outstanding transaction.
If the client initiates a timeout, the behavior is exactly the same: the execution stops immediately, the outstanding transaction is neither committed nor rolled back, and an unhandled exception is sent to the client. This is simply how timeouts work, and the only way to avoid this behavior is to turn it off altogether. For instance, we can turn off timeouts in ADO.NET by setting the CommandTimeout property to 0. Of course, we can turn XACT_ABORT on, in which case at least the transaction will be rolled back. The CATCH block, however, will still be bypassed.
Even more surprising for object-oriented developers is that this is not a bug; it is just the way SQL Server works in this case. According to MSDN for SQL Server 2008:
Doomed transactions
Listing 8. A transaction is doomed after a trivial error such as a conversion error.
As the output demonstrates, we can commit a transaction after a divide by zero, but a conversion error renders the transaction doomed, and therefore uncommitable. The latter case demonstrates that even a seemingly trivial conversion error is considered severe enough to override the XACT_ABORT setting, and the whole transaction is automatically rolled back.
To determine whether or not our transaction is committable, within TRY...CATCH, we can use the XACT_STATE() function, as demonstrated in Listing 9.
Listing 9. Using xact_state to determine if our transaction is committable or doomed.
Clearly, there are situations where the concept of a doomed transaction makes sense. For example, if the server runs out of disk space while running a transaction, there is no way the transaction could complete. Unfortunately, the current implementation of SQL Server sometimes dooms transactions for very trivial reasons. In all too many cases, this peculiar behavior of SQL Server makes it impossible to develop feature-rich error handling in T-SQL because, if a transaction is doomed, we have no choice other than to roll it back.
We will not cover any examples here, but this can also cause problems when attempting to use SAVEPOINTs. Consider the following, very common, requirement:
TRY...CATCH blocks cannot catch all errors
Interestingly enough, sometimes TRY...CATCH blocks just do not catch errors. This sometimes represents "expected behavior;" in other words, the behavior is documented and the reason why the error is not caught, for example when a connection fails, is intuitive. However, in some other cases the behavior, while still documented, can be quite surprising.
In either case, however, it means that we cannot assume that all errors originating in the database can, or will, be handled in a TRY...CATCH. Whenever we issue an SQL statement from the client, we need to be aware that it can generate an exception, and we need to be ready to handle it on the client, in case the TRY...CATCH blocks that we use in our T-SQL code don't catch it.
Killed connections and timeouts
In some cases, it is the expected behavior that errors cannot be caught by TRY...CATCH blocks. For example, if your connection is killed, it is documented and well known that your CATCH block will not catch and handle it.
Also, we need to be aware of "attentions," also known as "timeouts," as they also cannot be caught by TRY...CATCH blocks, and this is also the expected behavior. To demonstrate this, start the script in Listing 5, but cancel its execution immediately by pressing the Cancel Executing Query button.
Listing 5. TRY...CATCH behavior when a timeout occurs.
The execution stops immediately, without executing the CATCH block. Listing 6 demonstrates that the connection is still in the middle of an outstanding transaction.
Listing 6. The connection is in the middle of an outstanding transaction.
If the client initiates a timeout, the behavior is exactly the same: the execution stops immediately, the outstanding transaction is neither committed nor rolled back, and an unhandled exception is sent to the client. This is simply how timeouts work, and the only way to avoid this behavior is to turn it off altogether. For instance, we can turn off timeouts in ADO.NET by setting the CommandTimeout property to 0. Of course, we can turn XACT_ABORT on, in which case at least the transaction will be rolled back. The CATCH block, however, will still be bypassed.
Problems with TRY...CATCH scope
In some cases, the behavior in TRY...CATCH is documented, but will be surprising to developers used to error handling in languages such as C#.
Listing 7 demonstrates a simple case of a query, wrapped in a TRY...CATCH, which tries to use a temporary table that does not exist. However, the CATCH block is not executed, and we get an unhandled exception.
Listing 7. Sometimes a CATCH block is bypassed when an error occurs.
Even more surprising for object-oriented developers is that this is not a bug; it is just the way SQL Server works in this case. According to MSDN for SQL Server 2008:
The issue here is that compilation errors that occur at run time (as a result of deferred name resolution) abort the rest of the scope, which is equal to the batch in directly submitted SQL, but only equal to the rest of the procedure in a stored procedure or function. So a TRY...CATCH at the same scope will not intercept these errors, but a TRY...CATCH on a different scope (regardless of being nested or not) will catch it.
My point here is simple: SQL Server does not always handle errors in a way object-oriented languages do. If we choose to use the error handling provided by SQL Server, we really need to learn it in detail or we will be in for some unpleasant surprises.
Doomed transactions
There is another serious problem with T-SQL TRY...CATCH blocks: in some cases an error that occurred inside a TRY block is considered so severe that the whole transaction is doomed, or, in other words, it cannot be committed. Theoretically, the concept of doomed transactions makes perfect sense. Unfortunately, some really trivial errors, such as conversion errors, render transactions doomed if we use TRY...CATCH provided by T-SQL. For example, consider the transactions shown in Listing 8. The first attempts to perform a 1/0 calculation, and the second, to convert a strong to an integer. We do not want to roll back the whole transaction if an error occurs, so we set XACT_ABORT to OFF.
Listing 8. A transaction is doomed after a trivial error such as a conversion error.
As the output demonstrates, we can commit a transaction after a divide by zero, but a conversion error renders the transaction doomed, and therefore uncommitable. The latter case demonstrates that even a seemingly trivial conversion error is considered severe enough to override the XACT_ABORT setting, and the whole transaction is automatically rolled back.
To determine whether or not our transaction is committable, within TRY...CATCH, we can use the XACT_STATE() function, as demonstrated in Listing 9.
Listing 9. Using xact_state to determine if our transaction is committable or doomed.
Clearly, there are situations where the concept of a doomed transaction makes sense. For example, if the server runs out of disk space while running a transaction, there is no way the transaction could complete. Unfortunately, the current implementation of SQL Server sometimes dooms transactions for very trivial reasons. In all too many cases, this peculiar behavior of SQL Server makes it impossible to develop feature-rich error handling in T-SQL because, if a transaction is doomed, we have no choice other than to roll it back.
We will not cover any examples here, but this can also cause problems when attempting to use SAVEPOINTs. Consider the following, very common, requirement:
No comments:
Post a Comment