How to write a t-sql program to catch the transaction error increased at the end

advertisements

I am new to T-SQL programming. I need to write a main procedures to execute multiple transactions. How could i structure the program so that each transaction will not abort. Instead, the procedure will raise the error and report them back to the main program in the output parameters after all the transaction finish running. Please provide me with pseudo code if you can. Thanks.


You need to follow the template from Exception handling and nested transactions

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', 16, 1, @error, @message) ;
    end catch
end
go

As you can see you can't always continue, because sometime the exception has already aborted the transaction by the time you catch it (the typical example being deadlock exception 1205). And you must use a savepoint and revert to the savepoint in case of exception, to keep the database consistent. However, you do not abort the caller's work, if possible.