Try-Catch Statement


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.

What Errors Are Trapped

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 

Functions of the Catch Block

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().

Try-Catch Statement with Explicit Transactions

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.

Deadlock Retries

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.

image from book
Deadlocks

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.

image from book

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

image from book
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).

image from book
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).

Try-Catch Statement Nesting

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

Error Handling Architecture: With Try-Catch Statements

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.




Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL &  .NET
Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET
ISBN: 0072262281
EAN: 2147483647
Year: 2006
Pages: 165

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net