Commit and Rollback transaction with examples in SQL Server Stored Procedure

Transactions are the part of Transaction Control Language (TCL) Commands in SQL Server. They are used to manage transactions in database. These are used to manage the changes made by DML statements. COMMIT and ROLLBACK are key concepts in the transactions, Commit are used to save the whole transaction. Rollback is used to undo all the changes made by the DML in current transaction.


Example Procedure :


CREATE PROCEDURE P_INS_DATA_LOG     
(     
@VAL1 VARCHAR(4)=null,     
@VAL2 VARCHAR(10)=null,     
@VAL3 VARCHAR(10)=null,     
@VAL4 VARCHAR(10)=null,     
@VAL5 VARCHAR(15)=null
) 
AS 
BEGIN 

 SET NOCOUNT ON;   
 
  BEGIN TRANSACTION                                                       
 
  BEGIN          
INSERT INTO TABLE1   (COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5)     
          VALUES     
          (@VAL1,@VAL2,@VAL3,@VAL4,@VAL5)     
 
    IF(@@ERROR <> 0)                                                       
     GOTO  QUITWITHROLLBACK                                                       
    ELSE      
     GOTO ENDSAVE      

 END     
     
 QUITWITHROLLBACK:                                                      
 IF (@@TRANCOUNT > 0)                                                       
 BEGIN                                                                                   
 ROLLBACK TRANSACTION                                                      
 END                                                          
     

 ENDSAVE:                                                       
 IF (@@TRANCOUNT > 0)                                                      
 BEGIN                                                      
 COMMIT TRANSACTION                                                      
 END       
      
END

GO