How to use Rollback Transaction in MSSQL stored procedure?

There are different methods to use commit and rollback transaction in sql. The below query will help you to use rollback and commit in simple store procedure. In the insert statement if you get any error in query exaction means that inserted transaction or inserted records will roll back all the records. If you don’t get any error in the insert state meant means the full truncation will permanently will save to sql database.

CREATE PROCEDURE [MyProcedure]   

@Id int,

@Value Varchar(50)

as                       

BEGIN TRANSACTION  

INSERT INTO My_Table(Id,Value) values (@Id,@Value)

if(@@error <> 0)                                                   

    begin                                                   

     rollback transaction                                                   

    end                                                   

   else                                                    

    begin                                                   

     commit transaction                                                   

    end

In every Insert and delete statement in sql query we should use the commit and rollback transaction. This is the one of best practices in the MSSQL query.  @@error will give you if you have any error in the transaction it will give you the count. The count is 0 means no error in the transaction.