Skip to main content

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?

NVM. Regarding the problem, the nested procedure should use savepoints. Using the guard won't prevent the error,