Stored Procedures and Transactions

It is a good idea to place transactions in all the stored procedures that you build. Transactions ensure that a piece of work is completed in entirety, or not at all. One of the classic examples is a banking transaction. You want all the debits and credits to complete properly or not at all. This is where transactions come in. When using a SQL Server backend, you should place all transaction processing inside the stored procedures that you build.

Types of Transactions

Two types of transactions exist: implicit transactions and explicit transactions. Implicit transactions happen regardless of what you do in your programming code. Each time that you issue an INSERT, UPDATE, or DELETEstatement, SQL Server invokes an implicit transaction. If any piece of an INSERT, UPDATE, or DELETE statement fails, the entire statement is rolled back. For example, if your DELETE statement attempts to delete all the inactive customers, and somewhere in the process a record fails to delete (for example for referential integrity reasons), SQL Server does not delete any of the records. Explicit transactions, on the other hand, are transactions that you define and control. Using explicit transactions you package multiple statements within BEGIN TRANSACTION and COMMIT TRANSACTION statements. In your error handling you include a ROLLBACK TRANSACTION statement. This ensures that all the statements complete successfully or not at all.

Implementing Transactions

As mentioned, you use the BEGIN TRANSACTION, COMMIT TRANSACTION, and ROLLBACK TRANSACTION statements to implement transactions. The following is a stored procedure that utilizes a transaction.

CREATE PROCEDURE procOrderDetailAddTransactions
@SalesOrderID int,
@CarrierTrackingNumber nvarchar(25),
@OrderQty smallint,
@ProductID int,
@UnitPrice money,
@UnitPriceDiscount money
AS
SET NOCOUNT ON
DECLARE @SalesOrderDetailID int, @LocalError int, @LocalRows int
BEGIN TRANSACTION
INSERT INTO Sales.SalesOrderDetail
(SalesOrderID, CarrierTrackingNumber, OrderQty,
ProductID, UnitPrice, UnitPriceDiscount)
VALUES
(@SalesOrderID, @CarrierTrackingNumber, @OrderQty,
@ProductID, @UnitPrice, @UnitPriceDiscount)
SELECT @LocalError = @@Error, @LocalRows = @@RowCount
IF NOT @LocalError = 0 or @LocalRows = 0
    BEGIN
    ROLLBACK TRANSACTION
    SELECT SalesOrderDetailID = Null, Error = @LocalError,
        NumRows = @LocalRows
    END
ELSE
    BEGIN
    COMMIT TRAN
    SELECT @SalesOrderDetailID = @@Identity
    SELECT OrderOrderDetailID = @SalesOrderDetailID, Error = 0, NumRows =
@LocalRows
    END

In the example, the BEGIN TRANSACTION starts the transaction. The procedure attempts to insert a row into the Sales.SalesOrderDetail table. It populates the @LocalError variable with the value returned from the @@Errorfunction, and the @LocalRows variable with the value returned from the @@RowCount function. An IF statement evaluates whether either @LocalError or @LocalRows is zero. If either variable contains zero, the procedure was unsuccessful at inserting the row. The ROLLBACK TRANSACTION statement is used to terminate the transaction, and the procedure returns error information to the caller. If neither variable equals zero, you can assume that the process completed successfully. The COMMIT TRANSACTION statement commits the changes and the procedure returns the status and identity information to the caller.

Reference:
http://www.sqlserver2005tutorial.com/Tutorial-Learn-Stored-Procedures-Transactions-Types-Implementing.html