In one of my stored procedure I am Inserting the data into 3 tables.
BEGIN TRANSACTION trans1
Insert into tbla(column1)
VALUES(@variable1)
Insert into tblb(columna)
VALUES(@variable2)
Insert into tbla(columnx)
VALUES(@variable3)
IF @@ERROR<>0
BEGIN
ROLLBACK TRANSACTION tran1