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.