SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table my_table (blah varchar(10))
create procedure my_sp
as
insert into my_table values ('proc');
select 'inserted in proc';
select 1/0
go;
insert into my_table values ('init');
1 rows affected

BEGIN TRY
     BEGIN TRAN
          INSERT into my_table values ('tran');
          --Business validation
          exec my_sp; --This should throw error
          select 'Commit Tran'
          COMMIT TRAN
END TRY
BEGIN CATCH
     select 'In CATCH Block'
     ROLLBACK TRAN;
 END CATCH
 select 'After END CATCH'

(No column name)

inserted in proc

go

(No column name)

In CATCH Block

(No column name)

After END CATCH

select * from my_table;

blah

init