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