Transactions with Stored Procedures

for RuBoard

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.

for RuBoard