Introduction
This article presents a pattern for stored procedures that implement transactions in Microsoft SQL Server’s Transact-SQL (TSQL) language. The pattern includes everything you need to manipulate data within a transaction using robust error handling.
The intended audience of this article is any software developer who designs stored procedures in SQL Server. More specifically, if you are just learning how to implement transactions in TSQL or you are searching for ways to incorporate error handling, this article is for you.
If you like what you see here, feel free to download the sample stored procedure and use it as a template for your own procedures. See the Download the Code side bar for instructions.
Although this article is primarily about using transactions, it is hard to do a good job of managing transaction commit and rollback without careful error handling. The sample includes error handling techniques that can be used in any stored procedure, not just transactional ones.
Creating a transaction and tracking state
Key Technologies and Concepts |
|
Microsoft SQL Server |
Working with transactions in SQL Server stored procedures is really very simple. You identify the start of your transaction with the BEGIN TRANSACTION statement, and you terminate the transaction with either the COMMIT TRANSACTION or ROLLBACK TRANSACTION statement. You can also use the SET TRANSACTION ISOLATION LEVEL statement to control locking behavior, but that is not a requirement of using transactions.
The sample procedure demonstrates a conservative but robust approach to dealing with transactional code. It carefully segments the logic that is transactional, performs rigorous error handling, and does what it can to guarantee a commit or rollback.
By "transactional code," I mean any series of SQL statements that update data and must either all succeed or else have no effect. The classic example of a transactional operation is an accounting update where both the debited account and credited account must be modified together or your books become out of balance.
The general pattern demonstrated by the sample is as follows:
- Perform all preliminary validation (including parameter checking) before entering the transactional code. The idea is to find everything that will cause a problem before you start updating the database. During the transaction, you focus on just the processing errors that can result from data manipulation.
- Wrap all statements that must succeed or fail together in a transaction.
- Check for errors after every statement in the transaction.
- Commit the transaction if all statements succeed, or roll back the transaction if any statement fails.
Although the overall process is simple, your code can become complex when you introduce the logic that checks for errors and correctly terminates the transaction. You may have seen stored procedures that have redundant logic sprinkled throughout the procedure to deal with terminating the transaction at the various potential points of failure. Ideally, there should only be one place where you either commit or roll back.
The sample procedure simplifies matters by separating the detection and reporting of errors from the logic that terminates the transaction. It uses a flag (@InTrans) to determine if a transaction was started, and it uses a return code (@ReturnCode) to determine whether or not the transaction was successful. These two variables give the termination logic enough information to make the commit/roll back decision.
Capturing error codes and row counts
It is critical in transactional code that you check for unexpected results after every statement that affects the consistency of the transaction. That means you need to capture both the error code that might have been thrown if the statement failed and the row count that might have been set if the statement succeeded. The only way to capture them both is to write a SELECT statement that saves both values at the same time. If you try to use the SET statement to get @@ERROR you will clear @@ROWCOUNT (and vice-versa). Instead, you code something like this:
-- Execute your DML here SELECT @RowCount = @@ROWCOUNT, @ErrorCode = @@ERROR IF @ErrorCode <> 0 -- Handle the error IF @RowCount = 0 -- Handle the operation failure here
You can handle the unexpected results separately, as demonstrated above, or you can combine them into one handler:
IF @ErrorCode <> 0 OR @RowCount = 0 -- Handle either error
Unfortunately, not all errors can be handled gracefully. Certain types of errors cause SQL Server to abort the stored procedure immediately, which does not give you a chance to detect the error with @@ERROR and handle it gracefully. These errors also leave the transaction hanging because you never get the chance to commit or roll back. For example, referencing a table that does not exist will cause this kind of error.
To my way of thinking, an invalid database object reference is a kind of "compile error" that SQL Server should catch when you create the stored procedure (if only it were a little smarter at parsing time). Unfortunately, that is not the case.
Reporting errors
The sample procedure declares a couple of variables that it uses just for the purpose of recording error information: @ReturnCode and @Message. If the procedure detects an error, it sets both of these values with information that identifies the problem.
The sample includes an integer parameter called Parameter1 that the procedure expects to have a value greater than zero. The procedure validates parameters at the top, before it starts a transaction and attempts any data manipulation.
/* Verify that required parameters were provided. */ IF @Parameter1 < 1 BEGIN SET @ReturnCode = 1 SET @Message = @Message + 'Parameter1 must be greater than zero.' GOTO ProcError END
You might notice that the sample uses return codes less than 100 for errors it detects prior to the transaction (validation errors) and 100 or higher for errors it detects within the transaction (operational errors). This distinction lets you take different action when you exit the procedure if you want to do so. For example, if the error is less than 100, you might just set the return code and not use RAISERROR.
Why would you bother to make the distinction? Well, it depends upon how carefully you separate your business layer from your data layer. In my opinion, anything done in a stored procedure is a data layer operation, so a stored procedure should never be the first line of defense for detecting business rule violations. In other words, if a business rule violation is detected in the procedure, then I view it as a logic error in the calling application code. Consequently, I always raise an error if anything unexpected happens.
If you prefer to check for business rule violations in your stored procedures and let the procedure report the violations to the calling application code, then you might want to skip the RAISERROR when a validation error occurs.
Using a single point of exit
An important principle of software design in any environment is to use a single point of exit from your procedures. What this means is that all logic paths exit through the same location in the procedure, regardless of any errors that may be detected.
There are a number of ways to enforce the single-point-of-exit philosophy. You could wrap every statement in an IF statement that makes sure @ReturnCode is still zero before continuing. Alternatively, you can take advantage of the GOTO statement and immediately jump to the exit point when an error is detected.
While I am the first to agree that GOTO can be misused to create spaghetti code, the sample does use the GOTO approach. My rationale is that GOTO is always used consistently and for one purpose only: To exit the procedure when an error is detected. Additionally, I always name the exit labels ProcExit and ProcError.
The sample procedure demonstrates a technique I use in every stored procedure that has error handling. The procedure includes a ProcExit label and a ProcError label. If any kind of error occurs during processing, the code jumps to ProcError. All flow, including error flow, exits the procedure through ProcExit. This code structure is always the last thing in the procedure, so normal control flow simply drops through the ProcExit label.
In its simplest form, the exit point looks like this:
ProcExit: RETURN(@ReturnCode) ProcError: RAISERROR(@Message,16,1) GOTO ProcExit
Committing and rolling back transactions
The practice of using a single point of exit has extra value in transactional procedures, which require a commit/rollback point. Using a little forethought, you can design your procedures so the error handling interferes minimally with the normal flow of logic and funnels all flow through a single exit point that includes your commit/rollback decision.
The sample includes code that looks like this:
ProcExit: /* Commit or abort the transaction. */ IF @InTrans = 1 BEGIN IF @ReturnCode = 0 BEGIN COMMIT TRANSACTION END ELSE BEGIN ROLLBACK TRANSACTION END END SET TRANSACTION ISOLATION LEVEL READ COMMITTED RETURN(@ReturnCode) ProcError: RAISERROR(@Message,16,1) GOTO ProcExit
The procedure exit code first verifies that you started a transaction. If you trapped a parameter error at the beginning of the procedure and exited with an error, you won’t have started a transaction yet, so there is no need to commit or roll back.
If you did start a transaction, the @InTrans flag will be set to 1. You can then choose to commit or rollback based on the return code you set during the execution of the transactional code. If @ReturnCode is still zero, then no errors occurred and you can commit; otherwise, you roll back.
Note that the stored procedure resets the transaction isolation level to the default value before exiting. Since that setting affects the entire connection, including any code that follows in a calling procedure, it is good practice to restore the default value before returning.
Using RAISERROR to raise an error
Download the Code |
|
The transactional stored procedure pattern presented in this article is available as a free download. Just right-click the link below and select the appropriate item from the popup menu (e.g. Save Link As, Save Target As, etc). |
Should an error occur during the processing of your stored procedure, you need a way to report that error back to the calling application or calling stored procedure. There are several ways to do this, and the best approach depends upon how you normally handle errors in your application.
There seems to be two major schools of thought on handling errors. The first group contends that every procedure should return a Boolean success flag, and you should check that flag after every call you make to gracefully handle any errors that occur. The second group contends that you should use the error handling features built into the environment to bubble errors up the call stack and handle those errors at the point where they affect logic flow.
After using both approaches, I have found that the second approach works best for me. I’ve discovered that hiding errors behind a Boolean flag is asking for trouble. Every example I’ve seen where someone uses this approach (including my own), included instances where the flag was not checked after a call. All it takes is for you to forget to check for errors in one place, and you’ve introduced a spot in your code where an error-raising bug can become virtually impossible to find.
SQL Server straddles the two camps with regard to its error handling features. On the one hand, you can use the RAISERROR statement to raise an error and provide very detailed information about what went wrong. On the other hand, you still must check for an error after any statement that might raise one (including procedure calls that triggered an error with RAISERROR). There is no mechanism (that I know of, anyway) that lets you jump to a label when an error is raised.
Unlike some programming environments, the SQL Server RAISERROR statement does not terminate the processing of the stored procedure. All it does is set a system flag and set @@ERROR. You can optionally write the error to the event log.
You have several options when using the RAISERROR statement. The sample creates ad-hoc error messages and uses the default user-defined error number of 50000. Alternatively, you can define all of your error messages in SQL Server using sp_addmessage, which puts them into the sysmessages table. If you take the sysmessages approach, you should call RAISERROR at the point of error and immediately jump to your exit point. Your system error message number makes a return code unnecessary.
Returning a return code
The sample sets a specific return code value for each error it detects, and uses the RETURN statement at the end of the procedure to report the code back to the calling application.
Using both RAISERROR and the RETURN statement has some interesting side-effects. If you use the RETURN statement after the RAISERROR statement as the sample does, you can’t use @@ERROR in a calling stored procedure to detect the error. You MUST use the return code instead, because the RETURN statement itself resets @@ERROR to zero. However, SQL Server still knows that an error has been raised, and it will still signal an error in any calling application code when you exit the top-level procedure.
You can use this behavior to your advantage. Within the data layer (i.e. while stored procedures are calling other stored procedures), you might very well be interested in the exact cause of a problem. You can use the return code to make that determination.
Once the error bubbles up into the application’s business layer, you may only care if the operation succeeded or failed, and then you raise an error in the event of a failure. You can log the message that bubbled up from the stored procedure or just ignore it.
Alternatively, you can choose to suppress errors in the application code, which lets you check the procedure’s return code. Whether or not your application can access the return code after an error has been raised could depend upon your environment, but I can report that there are no problems with inspecting the return value in your catch block when using ADO in .NET.
Message format and return code standards
On of the first things you should do before you adopt a pattern for dealing with errors in stored procedures is to define some standards regarding how your messages should be formatted and how return codes should be defined.
The sample implements a very simple standard: All error messages must be prefixed by the name of the stored procedure that generated them, all pre-transaction validation checks must use return codes of less than 100, and all in-transaction errors that are detected must use return codes greater than 100.
The sample sets the @Message variable to the stored procedure name right after the variable declaration. When an error is detected, the code simply appends the specific message to the stored procedure name. You don’t need to worry about testing @Message to detect errors because you use @ReturnCode in parallel with it, and @ReturnCode is your definitive variable for reporting and detecting errors.
When nesting stored procedures, you usually want any error to bubble up from the procedure that originated the error. For that reason, you should check the return code from every procedure call and immediately drop to ProcExit if you get a non-zero value. That way you don’t obscure the original error by raising a new one.
SQL transactions versus ADO transactions
This article is about performing transactions within stored procedures, but in most software environments you can control transactions from outside SQL Server. For instance, if your application uses ADO, you can use it to perform your transactional processing instead.
The right place to perform transaction processing depends upon your circumstance. If your application always uses database systems that provide robust stored procedure languages and transactional processing, then you almost certainly want to shift large chunks of your data layer logic down into the database server. It is just more efficient that way.
On the other hand, if your application must support a wide variety of databases with varying capabilities (Access and SQL Server, for example) you probably want to keep most of the data layer logic in the application environment. As usual, you trade performance for flexibility.
Conclusion
Although using transactions in Transact-SQL is conceptually very simple, handling errors properly adds complexity to your code. This tendency is nothing unusual, since error handling often accounts for a significant percentage of your coding in any environment.
This article showed you a way to manage the complexity using a pattern that provides robust error handling, yet simplifies the logic flow through your stored procedures.
See the Download the Code side bar for instructions if you’d like to download the transactional stored procedure pattern template presented in this article and adapt it to your own projects.
