You may argue that the line
IF @@trancount > 0 ROLLBACK TRANSACTION is not needed if there no
explicit transaction in the procedure, but nothing could be more
wrong. Maybe you call a stored procedure which starts a transaction,
but which is not able to roll it back because of the limitations of
TRY-CATCH.
Link: https://www.sommarskog.se/error_handling/Part1.html
Code provided is:
CREATE PROCEDURE insert_data @a int, @b int AS
SET XACT_ABORT, NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION
INSERT sometable(a, b) VALUES (@a, @b)
INSERT sometable(a, b) VALUES (@b, @a)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@trancount > 0 ROLLBACK TRANSACTION
DECLARE @msg nvarchar(2048) = error_message()
RAISERROR (@msg, 16, 1)
RETURN 55555
END CATCH
Since SET XACT_ABORT is ON, and if instead of insert there was stored procedure execution, and say we didn’t have the following:
IF @@trancount > 0 ROLLBACK TRANSACTION
-
What are the limitations of TRY-CATCH that would prevent the rollback within the other SP even in the presence of SET XACT_ABORT ON?
-
Why isn’t the rollback happening in this SP for an error in the underlying SP given that SET XACT_ABORT IS ON?