When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
Technology Jobs
ASP ASP.NET ASP FAQs Message Board Feedback ASP Jobs
Print this page.
Published: Wednesday, August 03, 2005

Managing Transactions in SQL Server Stored Procedures

By Scott Mitchell


Introduction
Last week's article, Maintaining Database Consistency with Transactions, looked at what, exactly, transactions are and how they can be used to guarantee consistency in a database when issuing commands that modify multiple tables. Specifically, transactions ensure that a set of modifying statements are atomic, namely that either all steps succeed or all steps fail. Transactions guarantee atomicity across query errors - such as trying to delete a record that cannot be deleted due to a foreign key constraint, or attempting to insert a string value into a numeric field in a database table - as well as catastrophic errors, such as power failures, hard drive crashes, and so on. In short, when wrapping multiple modifying commands within a transaction the database is guaranteed to be in one of two states: either the state before the batch of commands was issued, or the state after all commands have completed - in other words, there's no 'in-between' state.

The canonical transaction example is transferring money from one bank account to another. This process involves two steps: debiting money from one account and then crediting it to the other account. What is vital to avoid is having only the first step complete but, perhaps due to a power failure, the second not completing. When moving funds we don't want the bank to debit money from our account if they're not going to credit it back to another.

In addition to discussing the purpose of transactions, last week's article also examined how to wrap multiple modifying statements within a transaction using ADO.NET. In particular we looked at using the SqlTransaction and SqlConnection classes. A transaction is started with a call to the SqlConnection class's BeginTransaction() method (which returns a SqlTransaction object) and can be committed or rolled back via the SqlTransaction object's Commit() and Rollback() methods.

In this week's article we'll continue our look at transactions, examining how to create, commit, and rollback transactions strictly through stored procedures. After examining the T-SQL syntax for working with transactions we'll discuss when one would opt to use transactions directly in stored procedures versus using them in the ADO.NET layer. Read on to learn more!

- continued -

'

A Bit About Stored Procedures...
A database's stored procedures are akin to computer program's methods - like a method, stored procedures can accept a variable number of input parameters and can optionally return data. With a stored procedure you can write a number of T-SQL statements that are executed when the stored procedure is invoked, much like how a method is a collection of statements that are executed when the method is called.

Stored procedures offer a number of advantages over ad-hoc SQL statements, including:

  • Adding another layer of encapsulation to the software architecture,
  • Separating the data-specific details from the source code of the application,
  • Security enhancements, as you can deny access to the database's underlying tables, instead only granting execute permissions for the stored procedures your web application needs to use, and
  • Performance increases since SQL server can cache the execution plan on stored procedures whereas such plans need to be recomputed each time with ad-hoc queries.
This article doesn't aim to provide an in-depth discussion on creating and using stored procedures or the benefits therein. For a more thorough discussion of stored procedures and their benefits can be found in Rob Howard's blog entry, Don't use stored procedures yet? Must be suffering from NIHS (Not Invented Here Syndrome). Nathan Pond's article Writing a Stored Procedure provides details on creating stored procedures in SQL Server. Further information can be found in the technical documentation.

Creating Transactions Within a Stored Procedure
Recall from Maintaining Database Consistency with Transactions that when using transactions you'll typically use the following sequence of steps:

  1. Indicate that you want to start the transaction. All commands from this point forward are part of the logical, atomic operation.
  2. Issue the discrete commands - the INSERT, UPDATE, and DELETEs that make up your transaction.
  3. If any of these commands cause an error, rollback the transaction. Rolling back a transaction has the effect of undoing the effects of all previous statements in the transaction.
  4. If all steps succeed, commit the transaction. This persists the changes made throughout the transaction to the database.
Recall that these steps were accomplished in ADO.NET through a call to the BeginTransaction() method, with the transaction's calls wrapped inside of a Try ... Catch block. In the Catch statement - which would be reached if there were any SQL-related errors, you'd rollback the transaction by calling the SqlTransaction object's Rollback() method. If, however, no errors occurred you'd commit the transaction by calling the Commit() method. The means for starting, committing, and rolling back a transaction inside of a stored procedure are a bit different, but the overall concept is the same.

First things first - we need to indicate that a transaction should begin. This is done, conveniently enough, with the T-SQL command BEGIN TRANSACTION. Following that, you'll issue the set of statements that you want to be part of the transaction, which will typically include INSERTs, UPDATEs, and/or DELETEs.

If you are using a version of Microsoft SQL Server prior to 2005, then after each and every modifying statement you'll need to check to see if some error has occurred and, if so, you'll need to rollback the transaction and raise an error. This can be done by checking the value of @@ERROR - if it's non-zero then the last statement caused an error. In the event of an error you'll first want to rollback the transaction using the keyword ROLLBACK and then raise an error using RAISERROR. RAISERROR will terminate the stored procedure call and cause an exception to be raised in your .NET application.

If you are using Microsoft SQL Server 2005 or onwards, then you can use alternatively use the TRY...CATCH block. This article looks at an example using the pre-SQL Server 2005 pattern. For a look at SQL Server 2005's TRY...CATCH block and how it can be used to create a more terse syntax for transactions, see: TRY...CATCH in SQL Server 2005: An Easier Approach to Rolling Back Transactions in the Face of an Error.

Lastly, at the end of the stored procedure, if there has been no error you'll want to commit the transaction, which can be accomplished using the COMMIT keyword.

An Example...
The following snippet shows a stored procedure that uses a transaction to maintain data consistency. In this particular example, there are two related tables, Departments and Employees, with the stored procedure deleting a specific department. Each Employees record has a foreign key constraint to the Departments table. Therefore, in order to delete a record from the Departments table all associated records from the Employees table must be deleted first. Since we want to ensure that either all of the associated employees and the department are deleted, or none of the records are deleted, we'll wrap these two DELETE statements within a transaction.

CREATE PROCEDURE DeleteDepartment
(
   @DepartmentID    int
)
AS

-- This sproc performs two DELETEs.  First it deletes all of the
-- department's associated employees.  Next, it deletes the department.

-- STEP 1: Start the transaction
BEGIN TRANSACTION

-- STEP 2 & 3: Issue the DELETE statements, checking @@ERROR after each statement
DELETE FROM Employees
WHERE DepartmentID = @DepartmentID

-- Rollback the transaction if there were any errors
IF @@ERROR <> 0
 BEGIN
    -- Rollback the transaction
    ROLLBACK

    -- Raise an error and return
    RAISERROR ('Error in deleting employees in DeleteDepartment.', 16, 1)
    RETURN
 END


DELETE FROM Departments
WHERE DepartmentID = @DepartmentID

-- Rollback the transaction if there were any errors
IF @@ERROR <> 0
 BEGIN
    -- Rollback the transaction
    ROLLBACK

    -- Raise an error and return
    RAISERROR ('Error in deleting department in DeleteDepartment.', 16, 1)
    RETURN
 END

-- STEP 4: If we reach this point, the commands completed successfully
--         Commit the transaction....
COMMIT

The RAISERROR method specifies the error message to return to the client along with the severity and state. Refer to the technical documentation for more information on RAISERROR.

Transaction Management: Stored Procedures or ADO.NET?
This article, along with last week's look at using ADO.NET to manage transactions, provide two different ways for providing transactional support in your data-driven applications. So which technique should you use? I find myself using both techniques. For simple batch statements that are known at compile time I tend to use stored procedures. An excellent example would be the case in point above, when deleting from a table that has associated child records that I also want to delete.

There are times, however, when I need to issue a series of atomic statements but I don't know what statements will need to be executed, exactly, until runtime. Or, there may be a variable number of stored procedure calls that need to be made based on data supplied by the user. For example, imagine that I presented a list of all departments in the system using a CheckBoxList Web control, allowing the user to select the set of departments she wanted to delete.

Upon posting back, I'd enumerate the items in the CheckBoxList's Items collection and issue a call to the DeleteDepartment stored procedure we examined above. Now, deleting each department and its associated employees is treated as a transaction (as we have BEGIN TRANSACTION in the stored procedure), but I want all department deletions to behave as an atomic statement. That is, say the user opted to delete five departments. If three departments and their employees to be deleted only to have an error crop up when trying to delete the fourth department, I want to make sure that the delete of the first three departments and employees are "undone." I can accomplish this by wrapping the loop through the Items collection and the subsequent calls to DeleteDepartment within a transaction itself. (This is an example of providing transactions at both the ADO.NET level and stored procedure level!)

That is, my code would look something like:

'Create a connection
Dim myConnection As New SqlConnection(myConnString)
myConnection.Open()

'Start the transaction    
Dim myTrans As SqlTransaction = myConnection.BeginTransaction()

Dim sql as String = "DeleteDepartment"

Try
   'Create the SqlCommand object, specifying the transaction through
   'the constructor (along with the SQL string and SqlConnection)
   Dim myCommand as New SqlCommand(sql, myConnection, myTrans)
   
   For Each dept as ListItem in CheckBoxListID.Items
      'Add the dept.Value to the Parameters collection
      myCommand.Parameters.Clear()
      
      myCommand.Parameters.Add("@DepartmentID", dept.Value)
      
      myCommand.ExecuteNonQuery()
   Next

   'If we reach here, all command succeeded, so commit the transaction
   myTrans.Commit
   
Catch ex as Exception
   'Something went wrong, so rollback the transaction
   myTrans.Rollback()
  
   Throw 		'Bubble up the exception
Finally
   myConnection.Close()	'Finally, close the connection
End Try

Conclusion
In a previous article we looked at maintaining transactions through ADO.NET; in this article we saw how transaction management can be done directly through T-SQL commands in stored procedures. Both approaches have their time and place - I typically manage transactions in the ADO.NET layer when the transactions involves iterating through programming constructs or other high-level logic needs to be applied when determining what entities to modify. However, when issuing a simple, straightforward request that modifies multiple tables - such as deleting a record from a "parent" table that requires that it's "children" also be subsequently deleted - I place the transaction logic at the stored procedure level. Of course, your mileage may vary.

Happy Programming!

  • By Scott Mitchell



  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article