Begin and Commit Transaction

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

GO

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.
http://www.sqlteam.com/article/introduction-to-transactions