Successful Strategies for TRY ... CATCH in SQL Server 2005

Question

I'm changing some code to take advantage of TRY ... CATCH in SQL Server 2005. What successful strategies have you found for using it? I'm thinking of creating a stored proc which calls the system functions which give details of the error, rolls back any open transaction and raises an error. Is there a better way?

Answer 1

I'm using TRY...CATCH extactly the way you described, using RAISERROR at the end of the CATCH-block to pass the error back to the caller. This also allows you to handle certain error codes within the stored procedure as well.

Answer 2

Here is a sample of the template I'm using:

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
     if @trancount = 0
      begin transaction
     else
      save transaction usp_my_procedure_name;

     -- Do the actual work here

lbexit:
     if @trancount = 0 
      commit;
    end try
    begin catch
     declare @error int, @message varchar(4000), @xstate int;
     select @error = ERROR_NUMBER()
                , @message = ERROR_MESSAGE()
                , @xstate = XACT_STATE();
     if @xstate = -1
      rollback;
     if @xstate = 1 and @trancount = 0
      rollback
     if @xstate = 1 and @trancount > 0
      rollback transaction usp_my_procedure_name;

     raiserror ('usp_my_procedure_name: %d: %s', 11, 1, @error, @message) ;
     return;
    end catch 
end

This templates handles nested transactions, each procedure only rolls back its own work (if possible) and lets the caller decide if to rollback or not the caller's work. The important thing is to check the XACT_STATE because of the possibility of doomed transactions. Also MSDN has quite a lengthy text on this very topic.

There are also various issues at stake that can change what you can and should do vis-a-vis TRY/CATCH and transaction management: Are you using distributed transactions? Do you have linked servers? Do you need restore-to-named-savepoint capabilities for some of your maintenance jobs? Do you need your caller to have access to the original exception code? Do you want to preserve exception severity, perhaps for your automated monitoring and alert notifications? Point being that some templates and practices work in some situations, some in other. The template I posted so far proved good for most of my cases.