SQL SERVER - How to use TRANSACTION in Stored Procedure | COMMIT, ROLLBACK

A SQL Server Transaction in a Stored procedure can be used to do a batch of queries especially INSERT / UPDATE / DELETE. The changes made to data in a transaction will persist only once COMMITTED. Hope the sample below helps you to get a practical idea about its usage.

BEGIN TRAN

INSERT INTO Person(Name, Age, Phone, Address)
VALUES ('Gates',50, '800-MSFT', 'Redmond, Washington, U.S')

IF @@ERROR <> 0
ROLLBACK TRAN
ELSE
        COMMIT TRAN