For a while now, there is a checkbox that lets your code run in an atomic transaction. This is a great improvement allowing you to easily let your code run inside a transaction.
I would like to propose an additional improvement, by letting the user put in a template within the catch-part of the atomic transaction. This way, you can add your own error handling in the catch part of the code as well.
Below an example of what this could look like:
CREATE or ALTER procedure demo
as
begin
--PART 1 of default transaction code, generated by default by Software Factory
begin try
begin transaction;
--Force an error
select 1/0;
--PART 2 of default transaction code, generated by default by Software Factory
commit transaction;
end try
begin catch
if @@trancount > 0
begin
rollback transaction;
end;
--USER ENTERED additional error handling
--for example, writing some error in a log.
insert into error_log (error_log_message)
values ('something went wrong');
--PART 3 of default transaction code, generated by default by Software Factory
throw;
end catch;
end
