Let's consider this code
begin try
begin transaction;
--control_proc_id: convert_camel_case_to_snake_case
--template_id: convert_camel_case_to_snake_case
--prog_object_item_id: convert_camel_case_to_snake_case
--template_description:
declare @to_tag_id big_text = dbo.camel_case_to_snake_case(@input)
exec task_rename_tag
@from_tag_id = @input
, @to_tag_id = @to_tag_id
if @@trancount > 0 -- ALWAYS CHECK FOR ACTIVE TRANSACTIONS
commit transaction;
end try
begin catch
if @@trancount > 0
begin
rollback transaction;
end;
; -- Throw original exception
throw;
end catch;
When the task “task_rename_tag” invokes a rollback, the calling procedure will try to commit the transaction when there is no guard that checks @@trancount.
I think it would be always a best practice to always check trancount, to prevent illegal commit or rollback. The “atomic” templates that are used can be easily expanded with the safeguard if @@trancount > 0
Is there any reason it isn't implemented, should I treat transactions differently?