A new feature in SQL Server is the Try-Catch statement. It consists of a single Try block and a single Catch block that must be placed one after the other. You can enclose a group of other Transact-SQL statements in the Try block. If an error occurs in the Try block, control is transferred to the group of statements in the Catch block. Naturally, when an error is trapped and handled (by executing statements in the Catch block), it will not be reported to the caller. When execution of the Catch block is completed, the engine will return control to the first statement after the Catch block. (There are some exceptions. We will explore them in the upcoming "Try-Catch Statement Nesting" section.) If execution of a statement in the Try block is completed without problems, the statements in the Catch block will be skipped and the engine will start executing the statement after the Catch block.
Create Procedure dbo.ap_ChargeLog_Insert @ItemId int, @ActionId smallint, @Cost money, @Note varchar(max), @Activity varchar(1000) as BEGIN TRY INSERT [dbo] . [ChargeLog] ([ItemId] , [ActionId] , [ChargeDate] , [Cost],[Note]} VALUES (@ItemId, @ActionId, GetDateO, @Cost, @Note) INSERT INTO [dbo] . [ActivityLog] ([Activity] , [LogDate], [UserName],[Note]} VALUES (@Activity, GetDateO, system_user, @Note) END TRY BEGIN CATCH INSERT INTO [dbo] . [ErrorLog] ([ErrorNum] , [ErrorType] , [ErrorMsg],[ErrorSource]) VALUES (50000,'E', 'Unable to record transaction in ChargeLog.', 'ap_ChargeLog_Insert') END CATCH Return
The stored procedure opens a transaction and then tries to execute two Insert statements. If either of the statements fails, the transaction will be rolled back and the record will be logged in a special table.
SQL Server 2005 is better than its predecessor at trapping a wider variety of errors. I'll now discuss which types of errors can be trapped and which cannot. The Try-Catch statement does not catch warnings:
BEGIN TRY Print 'Begin try' INSERT [dbo].[ChargeLog]([ItemId],[ActionId],[ChargeDate],[Cost],[Note]) VALUES (30, 15, GetDate(), $150, null) raiserror ('Some Error!', 10, 1) INSERT INTO [dbo] . [ActivityLog] ([Activity] , [LogDate] , [UserName] , [Note]) VALUES ('Repair',GetDate(),system_user, null) Print 'End try' END TRY BEGIN CATCH print 'Catch' INSERT INTO [dbo] . [ErrorLog] ([ErrorNum] , [ErrorType] , [ErrorMsg] , [ErrorSource]) VALUES (50000,'E', 'Unable to record transaction in ChargeLog.','ap_ChargeLog_Insert') END CATCH Print 'Finished!'
If you execute the preceding example, you will see that the Try block ignores the warning and continues as though nothing happened:
Begin try (1 row(s) affected) Some Error! (1 row(s) affected) End try Finished!
One big problem in previous versions was that fatal errors automatically led to discontinuation of processing. This version of SQL Server is much better at trapping and resolving fatal errors (with a severity level equal to or higher than 17). If the connection is not lost, the SQL Server engine will attempt to handle the error and continue.
In the following example, I'll raise a fatal error.
BEGIN TRY print 'Begin try' raiserror ('Some Error!', 23, 1} print 'End try' END TRY BEGIN CATCH print 'Catch' END CATCH Print 'Finished!'
SQL Server 2005 will trap this error and continue with the code in the Catch block:
Begin try Catch Finished!
Now, let's try the equivalent code in SQL Server 2000:
print 'start' Raiserror('Some error!', 23, 1} With LOG if @@error <> 0 print 'Error detected!'
In this case, SQL Server 2000 will automatically break the connection:
start Server: Msg 50000, Level 23, State 1, Line 2 Some error! Connection Broken
Earlier versions of SQL Server failed to process some errors of severity levels even lower than 17. In the following example, I'll try to assign a date to an integer variable:
declare @i int print 'start' set @i = '2/2/2005' if @@error <> 0 print 'error occurred' print 'finished'
Unfortunately, SQL Server 2000 abruptly stops execution of the stored procedure or batch:
start Server: Msg 245, Level 16, State 1, Line 4 Syntax error converting the varchar value '2/2/2005' to a column of data type int.
Let's try the equivalent code in SQL Server 2005:
BEGIN TRY print 'Begin try' declare @i int set @i = '2/2/2' print 'End try' END TRY BEGIN CATCH print 'Catch' END CATCH print 'Finished'
As expected, the engine traps the error:
Begin try Catch Finished
There is a set of special error handling functions that works only inside of the Catch block:
Function | Purpose |
---|---|
Error_Message() | Returns the error message that would normally be returned to the caller application |
Error_Number() | Returns the identifier of the error |
Error_Severity() | Returns the severity |
Error_State() | Returns the state |
Error_Procedure() | Returns the name of the procedure (or other programmatic database object) in which the error has occurred |
Error_Line() | Returns the line number of the procedure in which the error has occurred |
An important new feature of these functions (as compared to @@Error) is that they keep their values in the Catch block. You can reference them multiple times. These functions are important for investigating the error and also for notifying the caller if there are problems. For example, the following procedure uses them to assemble a custom error message that will be stored in the error log, and then raises the error to notify the caller application:
Alter Procedure dbo.ap_ChargeLog_Insert2 @ItemId int, @ActionId smallint, @Cost money, @Note varchar(max), @Activity varchar(1000) as BEGIN TRY INSERT [dbo].[ChargeLog]([ItemId],[ActionId],[ChargeDate], [Cost],[Note]) VALUES (@ItemId, @ActionId, GetDate(), @Cost, @Note) INSERT INTO [dbo].[ActivityLog]([Activity],[LogDate], [UserName] , [Note]) VALUES(@Activity, GetDate(), system_user, @Note) END TRY BEGIN CATCH declare @severity int set @severity = Error_Severity() declare @msg varchar(255) set @msg = 'Unable to record transaction in ChargeLog.' + 'Error(' + ERROR_NUMBER() + '):' + ERROR_MESSAGE() + ' Severity = ' + ERROR_SEVERITY() + ' State = ' + ERROR_STATE() + ' Procedure = ' + ERROR_PROCEDURE() + ' Line num. = ' + ERROR_LINE() INSERT INTO [dbo] . [ErrorLog] ([ErrorNum] , [ErrorType] , [ErrorMsg] , [ErrorSource]) VALUES (ERROR_NUMBER(), 'E', @msg, ERROR_PROCEDURE()) RAISERROR (@msg, @severity, 2) END CATCH Return
The last statement in the Catch block is re-throwing the error to the caller. Note that I could not use the Error_Severity() function in Raiserror(). Only values and variables are allowed in Raiserror().
The first thing that you should note regarding Try-Catch statements and transactions is that there is nothing magical—you have to roll back the transaction in the Catch block manually.
Alter Procedure dbo.ap_ChargeLog_Insert_wTran @ItemId int, @ActionId smallint, @Cost money, @Note varchar(max), @Activity varchar(1000) as BEGIN TRY BEGIN TRAN INSERT [dbo] . [ChargeLog] ([ItemId] , [ActionId] , [ChargeDate], [Cost],[Note]) VALUES (@ItemId, @ActionId, GetDate(), @Cost, @Note) INSERT INTO dbo.ActivityLog(Activity, LogDate, UserName, Note) VALUES (@Activity, GetDate(), system_user, @Note) COMMIT TRAN END TRY BEGIN CATCH ROLLBACK TRAN INSERT dbo.ErrorLog(ErrorNum,[ErrorType],[ErrorMsg],[ErrorSource]) VALUES (50000,'E', 'Unable to record transaction in ChargeLog.', ERROR_PROCEDURE(}} END CATCH return
Theoretically, it is possible when an error occurs to investigate the state of a transaction further. The new SQL Server 2005 function Xact_State() can test the state of the transaction. The function returns 0 if no transaction is open. It returns 1 if a transaction is open and can be committed or rolled back. When it returns −1, the opened transaction is uncommittable. In this state, no modifications are possible. This typically occurs when something dramatic happens on the server (e.g., when the transaction log becomes full). Data can be read and all locks are preserved in place (so you can investigate everything), but in order to continue modifications, you must issue a rollback.
It is debatable how logical or justified the following stored procedure may be, but it illustrates the use of the Xact_State() function. The transaction is relatively complex, consisting of several Insert and Select statements. Therefore, the Catch block is also more complex. It first handles the two simpler cases in which a transaction is uncommittable or not open. In these cases, error handling is straightforward—the transaction should be rolled back or ignored. Most interesting is the case in which a transaction is committable. You have to perform additional checks before deciding whether to commit or roll back:
Alter Procedure dbo.ap_ChargeLog_Insert_wTranState @ItemId int, @ActionId smallint, @Cost money, @Note varchar (max) , @Activity varchar(1000) as declare @Today smalldatetime declare @User sysname declare @ErrorCode int declare @EqId int declare @Price money BEGIN TRY select @Today = GetDate() set @User = system_user BEGIN TRAN INSERT [dbo].[ChargeLog]([ItemId],[ActionId],[ChargeDate], [Cost] , [Note]) VALUES (@ItemId, @ActionId, @Today, @Cost, @Note) select @EqId = EqId from dbo.Orderltem where ItemId = @ItemId select @EqId = EqId from dbo.OrderItem where ItemId = @ItemId select @Price = Price from dbo.PriceList where EqId = @EqId INSERT INTO dbo.Sales(EqId, [UnitPrice], [Qty], [ExtPrice] ,[SalesDate]) VALUES (@EqId, @Price, 1, @Price, @today) INSERT INTO dbo.ActivityLog(Activity, LogDate, UserName, Note) VALUES (@Activity, @Today , @User, @Note) COMMIT TRAN END TRY BEGIN CATCH set @ErrorCode = Error_Number() if xact_state() = -1 begin -- transaction is uncommittable ROLLBACK TRAN INSERT dbo.ErrorLog(ErrorNum, ErrorType, ErrorMsg, ErrorSource, ErrorState) VALUES (@ErrorCode, 'E', 'Unable to record transaction in ChargeLog.', ERROR_PROCEDURE(), -1) end else if xact_state() = 0 begin --error occurred before tran started INSERT dbo.ErrorLog(ErrorNum, ErrorType, ErrorMsg, ErrorSource, ErrorState) VALUES (@ErrorCode,'E', 'Unable to pre-process ChargeLog transaction.', ERROR_PROCEDURE(), 0) end else if xact_state() = 1 begin --error could be committed or rolled back commit tran if exists(select * from dbo.ActivityLog where Activity = @Activity and LogDate = @Today and UserName = @User) begin INSERT dbo.ErrorLog(ErrorNum, ErrorType, ErrorMsg, ErrorSource, ErrorState) VALUES (@ErrorCode,'E', 'Unable to record transaction in ActivityLog.', ERROR_PROCEDURE(), 1) end if exists(select * from dbo.Sales where EqId = @Activity and [SalesDate] = @Today) begin INSERT dbo. VALUES 'E','Unable to record transaction in', ERROR_PROCEDURE(), 1) end end END CATCH return @ErrorCode
Note | I do not think that the design of this procedure is justifiable. Instead of performing additional checks to do error handling when the transaction is still committable, I should have split the Try block into multiple Try blocks and handled each case in a separate Catch block. Or, if all steps need not be completed to commit the transaction, then the transaction should be split into two or more transactions. |
One consequence of the wider set of errors that a Try-Catch block can trap in SQL Server 2005 is that you can create a Transact-SQL script to handle a deadlock.
A deadlock is a situation that occurs in SQL Server when two connections are competing for resources at the same time and blocking each other's completion. There are several types of deadlocks, but the textbook example occurs when:
Connection 1 locks resource (table) A and changes it.
Connection 2 locks resource (table) B and changes it.
Connection 1 tries to acquire a lock on table B, but it has to wait until connection 2 completes its transaction.
Connection 2 tries to acquire a lock on table A, but it has to wait until connection 1 completes its transaction.
SQL Server detects the deadlock and decides to kill one of the connections. Error 1502 is raised.
The other connection completes the transaction.
First, let's try to simulate a deadlock. I have created two stored procedures each of which contains two modification statements and one WaitFor statement between them. The purpose of the WaitFor statement is to give me 10 seconds to operate Management Studio to execute the stored procedures so that their executions overlap. Note that the stored procedures access the same tables but in reverse order:
Alter Procedure [dbo].[ap_SalesByDate_IncreasePrice] @Factor real, @Date smalldatetime as set xact_abort on begin tran update dbo.Sales set UnitPrice = UnitPrice * @Factor, ExtPrice = ExtPrice * @Factor where SalesDate = @Date waitfor delay '0:00:10' update dbo.PriceList set Price = Price * @Factor commit tran return GO ALTER procedure [dbo].[ap_PriceByEqId_Set] @EqId int, @Price money as set xact_abort on begin tran update dbo.PriceList set Price = @Price where EqId = @EqId waitfor delay '0:00:10' update dbo.Sales set UnitPrice = @Price, ExtPrice = @Price * Qty where EqId = @EqId commit tran return
If you execute the procedures from two Query windows in Management Studio at approximately the same time (you have 10 seconds to start the second after the first), after some time SQL Server will detect a deadlock and kill one of the connections so that the other can continue (see Figure 6-2).
Figure 6-2: Two connections after deadlock
Deadlock errors in earlier versions of SQL Server resulted in a broken connection. In SQL Server 2005, they can be detected like any other error. Let's change one procedure to detect the error and retry execution after a delay in the loop:
ALTER procedure [dbo].[ap_PriceByEqId_Set_wRetry] @EqId int, @Price money as -- exec ap_PriceByEq!d_Set_wRetry 1, $16.82 declare @i int set @i = 1 while @i <= 10 begin begin try set xact_abort on begin tran update dbo.PriceList set Price = @Price where EqId = @EqId waitfor delay '0:00:10' update dbo.Sales set UnitPrice = @Price, ExtPrice = @Price * Qty where EqId = @EqId commit tran ------------ print 'completed' break end try begin catch if ERROR_NUMBER() = 1205 begin rollback tran set @i = @i + 1 print 'retry' INSERT INTO [dbo].[ErrorLog]([ErrorNum],[ErrorType],[ErrorMsg] , [ErrorSource] , [CreatedBy] , [CreateDT] , [ErrorState]) VALUES(Error_Number(), 'E', Error_Message(), Error_Procedure () , suser_sname () , GetDateO, Error_State ()) waitfor delay '0:00:03' end end catch end print 'Completed' return
When you execute this stored procedure, it recovers from the error and succeeds the second time (see Figure 6-3).
Figure 6-3: Execution of stored procedures with loop for handling deadlock error
Note | This type of deadlock error is called a cycle deadlock. The best way to handle cycle deadlocks is to change the order in which tables are modified. Changing the order would prevent a deadlock from occurring. Retry is the last resort for handling deadlocks and should he used only if they cannot he prevented (as in the case of key deadlocks). |
It is possible to nest Try-Catch statements. For example, it is possible to have a Try-Catch statement inside a Try block. It is also possible to call a stored procedure that has a Try-Catch block from an Exec statement inside a Try block. When the error occurs, the engine will start the executing statements in the last (inner) Catch block.
There is one behavior that I want to highlight: from where will SQL Server continue execution when the error is trapped and processed in the Catch block? As I explained earlier, if the batch has a single Try-Catch block, then execution will be continued from the first statement after the Catch block. In the case of a nested stored procedure that does not have a Try-Catch block, the execution will be continued from the statement after the (caller) Catch block that has processed the error. But if the nested procedure has a Try-Catch block as well, its Catch block will process the error, but process will continue from the first statement after the Exec statement that launched the nested stored procedure (not the first statement after the nested Catch).
We have explored different features of error handling, but the important question is what should you use for standards or guidelines on your next project.
The Try-Catch statement is very powerful and it can detect many more problems than was possible (with @@Error) in previous versions. You have two options. First, you can decide to wrap everything in every stored procedure in Try-Catch statements and use some generic error handling to pick up all errors, handle those that it recognizes, and re-throw others to the caller. This requires you to add a large amount of code and to make many agreements with client development teams about the meaning of custom errors, but in the end, the benefits are debatable. However, this option may be effective in some cases—for example, if you are required to log all errors in a table for audit purposes.
The other option is to use the Try-Catch statement only in places where you can implement some meaningful (i.e., useful) error handling. Basically, it should be used to cover real exceptions and not as a blanket to cover all possible errors. In such an architecture, all unexpected errors should be handled by the client application (or middleware components). A generic error handler must be created on the client side anyway because SQL Server cannot detect all errors. I prefer this option.