Stored Procedure with rollback in SQL Server - Exceptional handling - Try Catch

 CREATE procedure [dbo].[pro_batchExtendUpdate2](@dey int)

as

begin

begin try

begin tran

truncate table TBL_BATCH_EXTEND

insert into TBL_BATCH_EXTEND

 select Batch_Code,getdate(),@dey from MST_BATCH 

 commit tran

 end try

 begin catch

 rollback tran

 select ERROR_MESSAGE()

 end catch

 end

reference:- https://www.sqlshack.com/how-to-rollback-using-explicit-sql-server-transactions/

https://www.c-sharpcorner.com/article/sql-server-rollback-transaction-completely-in-case-of-error/