Correct use of transactions in SQL Server

I have 2 commands and need both of them executed correctly or none of them executed. So I think I need a transaction, but I don't know how to use it correctly.

What's the problem with the following script?

BEGIN TRANSACTION [Tran1]
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]
GO

The INSERT command is executed, but the UPDATE command has a problem.

How can I implement this to rollback both commands if any of them have an error in execution?

3 Answers

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 TRAN
SELECT * FROM T; -- Check after.
DELETE T;