Correct use of transactions in SQL Server

Add a try/catch block, if the transaction succeeds it will commit the changes, if the transaction fails the transaction is rolled back:

BEGIN TRANSACTION [Tran1]  BEGIN TRY      INSERT INTO [Test].[dbo].[T1] ([Title], [AVG])      VALUES ('Tidd130', 130), ('Tidd230', 230)      UPDATE [Test].[dbo].[T1]      SET [Title] = N'az2' ,[AVG] = 1      WHERE [dbo].[T1].[Title] = N'az'      COMMIT TRANSACTION [Tran1]  END TRY  BEGIN CATCH      ROLLBACK TRANSACTION [Tran1]  END CATCH  

At the beginning of stored procedure one should put SET XACT_ABORT ON to instruct Sql Server to automatically rollback transaction in case of error. If ommited or set to OFF one needs to test @@ERROR after each statement or use TRY ... CATCH rollback block.

Easy approach:

CREATE TABLE T(    C [nvarchar](100) NOT NULL UNIQUE,);SET XACT_ABORT ON -- Turns on rollback if T-SQL statement raises a run-time error.SELECT * FROM T; -- Check before.BEGIN TRAN    INSERT INTO T VALUES ('A');    INSERT INTO T VALUES ('B');    INSERT INTO T VALUES ('B');    INSERT INTO T VALUES ('C');COMMIT TRANSELECT * FROM T; -- Check after.DELETE T;