how to write a t-sql program to catch transaction raised error at the very end

Question

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.

Answer 1

You could use try/catch

BOL - TRY/CATCH

Here's an example

I have previously encapsulated logic into stored procedures and put in exec statements in the TRY/CATCH block. In the CATCH you can use this link to get error information (example B in the link)

BOL - ERROR_MESSAGE

Something similar to -

BEGIN TRY
    BEGIN TRAN
    EXEC StoredProcedure01
    EXEC StoredProcedure02
    COMMIT
END TRY
BEGIN CATCH
    ROLLBACK TRAN
    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;
GO

Answer 2

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.

Answer 3

I might consider trying this. Each transaction is a separate stored proc with a an overall stored proc that calls each in turn. Save the error information you want in a table variable. IN the catch block of each proc, rollback that transaction and then insert the data form the table variable with the error information into a logging table. Do not return a failure to the calling proc.

If you want to report the errors out of the main proc in real time, you can do a select from the logging table at the end.

It would work best if you create a batchid at the start of the calling proc and have that be an input variable to each of the procs you call and also include that data in the information you add to to the logging table. Then if the procs fail multiple times during nonworking hours, you have the errors for all of them and can see the batch they were associated with. This helps tremendously in tracking down problems.

You will need to give some thought as to what information you will want for each proc when designing your logging table. I would suggest that part of what you store is any input variables that are sent into the proc. Also if you are using dynamic SQl, then store the generated sql as well. If you can identify the user who ran the proc, that too is useful for tracking down permissions issues for instance.

Having a logging table is far more useful than just returning errors at run time. You can look for trends, see if the same error is frequently happening, look at the information that caused failures and the information that succeeded if you choose to also log the variables for successful runs.

None of this is out of the box easy to write code. It requires a great deal of design on your part to determine, exactly what information will be useful over time in troubleshooting issues with the process. How detailed you need to get is a business decision based on both how the data is used and what you will want to know about a failure. As such, we cannot make this determination for you.