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 a 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 stored procedure, even in the presence ofSET XACT_ABORT ON
? -
Why isn’t the rollback happening in this stored procedure for an error in the underlying stored procedure given that
SET XACT_ABORT
isON
?