Home > Articles > Programming > Windows Programming

  • Print
  • + Share This
This chapter is from the book

Transactions with Stored Procedures

Database transactions are by no means limited to ADO.NET. In fact, transactions are powered by the data source. That is to say, when you initiate a transaction inside ADO.NET, the data source (in this case Microsoft SQL Server) actually performs all the work.

To illustrate this point, the example in Listing 18.4 demonstrates how to perform a database transaction inside a stored procedure. It's very similar to an example used in a previous hour that adds an employee to the Employees table of the Northwind SQL Server database. This procedure also assigns a territory to the newly added employee as well, all rolled into a transaction.

To start a transaction inside a stored procedure in Microsoft SQL Server, you use the BEGIN TRAN keywords. To roll back a transaction, you use ROLLBACK TRAN and as you might have guessed, to commit a transaction, you use COMMIT TRAN.

Listing 18.4 Rolling a Transaction Back to a Saved Point

CREATE PROCEDURE Employee_Add
(
  @LastName nvarchar(20),
  @FirstName nvarchar(10),
  @Title nvarchar(30),
  @BirthDate datetime,
  @HireDate datetime,
  @Address nvarchar(60),
  @City nvarchar(15),
  @PostalCode nvarchar(10),
  @TerritoryID nvarchar(20)
)

AS

  DECLARE @iCommunityProductAuditID int

  BEGIN TRAN

  -- add the main record
  INSERT INTO Employees
  (
    LastName,
    FirstName,
    Title,
    BirthDate,
    HireDate,
    Address,
    City,
    PostalCode
  )
  VALUES
  (
    @LastName,
    @FirstName,
    @Title,
    @BirthDate,
    @HireDate,
    @Address,
    @City,
    @PostalCode
  )

  IF @@ERROR <> 0 
  BEGIN
   ROLLBACK TRAN
   RETURN @@ERROR
  END

    -- get EmployeeID
  declare @EmployeeID int
  SET @EmployeeID = @@IDENTITY

  -- add employee to a territory
    INSERT INTO EmployeeTerritories
    (
      EmployeeID,
    TerritoryID
    )
  VALUES
  (
    @EmployeeID,
    @TerritoryID
  )

  IF @@ERROR <> 0 
  BEGIN
   ROLLBACK TRAN
   RETURN @@ERROR
  END

  COMMIT TRAN

  RETURN @@ERROR
GO

The Employee_Add stored procedure in Listing 18.4 accepts a relatively large list of parameters in lines 3–11. As you'll recall from Hour 15, "Working with Stored Procedures," these are the same as function arguments. Line 18 begins a transaction within the stored procedure. Lines 21–42 add a record into the Employees table using the values supplied in the parameters.

If any errors were encountered while performing the INSERT query, the transaction is rolled back using the ROLLBACK TRAN SQL statement in lines 44–48. In line 52, the automatically incremented identity number created for the newly added employee is assigned to the @EmployeeID variable. The @EmployeeID is then used in lines 55–64 to add a territory for that new employee to the EmployeeTerritories table. Again, lines 66–70 ensure that if any errors were encountered, the entire transaction is rolled back; this means that not only will the EmployeeTerritories entry be removed, but also the entry made for the new employee in the Employees table. On line 72, the transaction is commited.

  • + Share This
  • 🔖 Save To Your Account