Use COMMIT and ROLLBACK Transaction in Store Procedure

 It is a good approach to put query in  try catch block , So if were using insert, update, delete it is a good approach to use commit and rollback as well, so if the query is failed it rollback the transaction



CREATE PROC Sp_NAME
AS    
Begin
    SET NOCOUNT ON;
Begin Try  
 BEGIN TRANSACTION

 --Do your insert update or delete logic whatever you want
    COMMIT TRANSACTION  
End Try  
 Begin Catch  
   ROLLBACK TRANSACTION  
 End catch  
END