Two options for executing transaction in MSSQL, each rollback by error.
First option:
BEGIN TRY
BEGIN TRAN
UPDATE 1
UPDATE 2 --Error
UPDATE 3
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
Second option:
SET XACT_ABORT ON
BEGIN TRAN
UPDATE 1
UPDATE 2 --Error
UPDATE 3
COMMIT TRAN
Both examples will done same actions, in case of error it will rollback transaction.