-- Оборачивать будем такую вот процедуру:
create procedure [dbo].[SomeProc] ( @P1 int = null, @P2 int = null, @P3 int = null output )
as
begin
  return 0;
end
go

create procedure [dbo].[SomeProc_Safe] ( @P1 int = null, @P2 int = null, @P3 int = null output )
as
begin

  --------------------------------------------------------------------------------  
  set nocount on;

  --------------------------------------------------------------------------------  
  declare @procName varchar(128) = object_name(@@procid);
  print 'executing: ' + @procName;
  print '@P1 = (' + isnull(cast(@P1 as varchar(10)), 'NULL') + ')';
  print '@P2 = (' + isnull(cast(@P2 as varchar(10)), 'NULL') + ')';
  
  set @P3 = null; -- output parameter
  -- ИЛИ
  print '@P3 = (' + isnull(cast(@P3 as varchar(10)), 'NULL') + ')';
  
  --------------------------------------------------------------------------------  
  declare @savepoint varchar(32) = null; -- имя save-point'а
  if (@@trancount = 0) begin
    begin transaction
    print 'New transaction has been started';
  end else begin
    set @savepoint = replace(newid(), '-', ''); 
    save transaction @savepoint
    print 'New savepoint has been created: (' + @savepoint + ')';
  end

  --------------------------------------------------------------------------------  
  -- Далее делаем вызов оборачиваемой процедуры.
  
  declare @rv int;
  exec @rv = SomeProc -- non-safe
    @P1 = @P1
  , @P2 = @P2
  , @P3 = @P3 output
  
  if (@rv != 0)
    goto failurePoint;

  --------------------------------------------------------------------------------
  goto successPoint;
		
--------------------------------------------------------------------------------  
failurePoint:

  print @procName + ': FAILURE'
  
	if (@sp is null)
	  rollback transaction
	else
	  rollback transaction @sp

  goto exitPoint;

--------------------------------------------------------------------------------  
successPoint:

  print @procName + ': SUCCESS'

	if (@sp is null)
	  commit transaction

  goto exitPoint;
  
exitPoint:

  print 'OUTPUT:@P3 = (' + isnull(cast(@P3 as varchar(10)), 'NULL') + ')';
  print @procName + ': has return (' + cast(@rv as varchar(10)) + ')'
  return @rv;
  
end
go

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
  
CREATE procedure ...

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
  
CREATE procedure ...