Transactions in SQL SERVER (Begin Tran, Commit, Rollback)

Create Proc TranTest
AS
BEGIN TRAN

INSERT INTO [authors]([au_id], 
 [au_lname], 
 [au_fname], 
 [phone], 
 [contract])
VALUES ('172-32-1176', 
 'Gates', 
 'Bill', 
 '800-BUY-MSFT', 
 1)

IF @@ERROR <> 0
   BEGIN
 ROLLBACK TRAN
 return 10
   END

UPDATE  authors
SET au_fname = 'Johnzzz'
WHERE au_id = '172-32-1176'

IF @@ERROR <> 0
   BEGIN
 ROLLBACK TRAN
 return 11
   END

COMMIT TRAN

You'll notice that we check each statement for failure. If the statement failed (i.e. @@ERROR <> 0) then we rollback the work performed so far and use the RETURN statement to exit the stored procedure. It's very important to note that if we don't check for errors after each statement we may commit a transaction improperly.

----Using TRY...CATCH to Rollback a Transaction in the Face of an Error---

CREATE PROCEDURE TranTest

( 

    @EmployeeID int

)
AS

BEGIN TRY
   BEGIN TRANSACTION    -- Start the transaction

   -- Delete the Employee's phone numbers

   DELETE FROM EmployeePhoneNumbers
   WHERE EmployeeID = @EmployeeID

   -- Delete the Employee record

   DELETE FROM Employees
   WHERE EmployeeID = @EmployeeID

   -- If we reach here, success!

   COMMIT

END TRY
BEGIN CATCH

  -- Whoops, there was an error

  IF @@TRANCOUNT > 0
     ROLLBACK

  -- Raise an error with the details of the exception

  DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
  SELECT @ErrMsg = ERROR_MESSAGE(),
         @ErrSeverity = ERROR_SEVERITY()

  RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH