I have a question regarding this tsql-code found in this thread regarding "nested transactions" (unfortunately my reputation is not large enough to comment in that thread): In what cases a transaction can be committed from inside the CATCH block when XACT_ABORT is set to ON?
Here is a procedure with transactions-check and a savepoint if the @@trancount > 0 But I don't see the difference the savepoint makes, when there is a "raiserror-statement" in the catch block of the procedure. Will this not force the calling code to rollback to the beginning of the calling code, thus ignoring the savepoint?
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; -- Insert into table: INSERT INTO TABLE1 'Data from usp_my_procedure_name' -- Make an error: SELECT 1/0 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
If this procedure is called in a nested way like this
CREATE OuterProc AS BEGIN TRY BEGIN TRANSACTION INSERT INTO TABLE1 'Data from OuterProc code' EXEC usp_my_procedure_name COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK END CATCH END
If I execute the OuterProc, the data 'Data from OuterProc code' will not get inserted. Then what is the point of the savepoint?
It works if I remove the raiserror-part of the code. Then the 'Data from OuterProc code' gets inserted..
So my question is - what is the point of the savepoint when there is a raiserror-statement in the procedures catch-block?