sp_Test1.sql

CREATE PROCEDURE [dbo].[sp_Test1]
@table1_Id INT OUTPUT
AS
begin try
	 begin transaction
		  print 'sp_Test1:executing sp_Test2'
		  exec sp_Test2 @table1_Id output
		  print 'sp_Test1:executing sp_Test3'
		  exec sp_Test3
		  print 'commit tran'
	 commit transaction
end try
begin catch
	 rollback transaction
	 print 'sp_Test1:in catch'
	 RAISERROR('raiseerror:sp_Test1', 16, 1)
end catch