How do I do transaction tracking within a stored procedure?
Hopefully, if you’ve been programming for any length of time against databases, you know that if you are updating multiple records in a database, you need to supply some sort of transaction tracking so that it either all works or all fails.
You do know that, right?
Well, maybe we should cover that in a future post.
But today, what happens if you have a stored procedure that updates multiple records in the database? I can’t depend on the programmer to implement transaction tracking because all he sees is one call. How is he supposed to know that it’s updating multiple records and should turn on transaction tracking in his code?
No. Transaction tracking is the responsibility of the programmer who wrote the stored procedure, and that’s what we are going to cover today.
In your stored procedure, to enable transaction tracking, your wrap your transaction code in the Begin Transaction and Commit Transaction statements.
Begin Transaction /* Code that must all work goes here */ Commit Transaction
One of the side effects of this block is that every line of code between the block will attempt to run. This is because you’ve said by using these statements, “Hey, I’m going to test for failure and deal with it myself.”
So, you might be asking, how do I deal with failures?
I’m so glad you asked. You deal with failure by testing the @@Error variable that’s built into SQL. If it is not zero, there has been an error.
So, for every command you execute in your code, you want a corresponding test for success.
And what do you do if it fails? You rollback, of course.
Begin Transaction DELETE FROM SomeTable WHERE someCondition=1 IF @@ERROR <> 0 BEGIN ROLLBACK Transaction RETURN END Commit Transaction
The one thing we’ve left out is that we need to tell the program that called this stored procedure that there is a problem. We do that in one of two ways.
First, you can return a numeric value.
Begin Transaction DELETE FROM SomeTable WHERE someCondition=1 IF @@ERROR <> 0 BEGIN ROLLBACK RETURN 23 END Commit Transaction
The problem with this method is that it assumes the programmer will actually check for the return value. And we know that is as likely to happen as not.
A better way is to have the code RAISE an exception
Begin Transaction DELETE FROM SomeTable WHERE someCondition=1 IF @@ERROR <> 0 BEGIN ROLLBACK RaisError ('Error deleting record at line 23',16,1) RETURN END Commit Transaction
The second two parameters indicate the severity and state. See the documentation for the specifics but these two variables take positive numbers and can mean whatever you want them to mean as long as Severity is between 0 – 18 (there are exceptions to this rule, see the documentation) and state is between 1 – 127.
Just like writing a try/catch block in your code, the real trick is in remembering to write the transaction tracking code in your stored procedure in the first place. But now that you know how, you have no excuse for not putting it in.
Other post in SQL For Programmers
- SQL For Developers - 9 Reasons to bother - August 13th, 2008
- MSSQL CREATE and DROP Database - SQL for Programmers - August 20th, 2008
- MSSQL CREATE TABLE - SQL For Programmers - August 22nd, 2008
- SQL For Programmers - ALTERing the TABLE - September 1st, 2008
- SQL For Programmers - Finding IN a List - September 8th, 2008
- SQL For Programmers - Stored Procedure Basics - September 16th, 2008
- Basic SQL Commands - SQL For Programmers - September 18th, 2008
- SQL IF/WHILE Blocks - SQL For Programmers - September 30th, 2008
- Temporary Tables - SQL For Programmers - October 7th, 2008
- SQL CURSOR - SQL For Programmers - October 13th, 2008
- SQL CURSOR Performance - SQL For Programmers - October 22nd, 2008
- Random in SQL - SQL For Programmers - November 4th, 2008
- SQL - Filtering WHERE condition on two rows - November 26th, 2008
- SQL - Transactions - April 15th, 2009
- SQL For Programmers – New Question - July 6th, 2009
- SQL SELECT CASE Instead of IIF - October 20th, 2009
- SQL For Programmers - Stored Procedures (Better than LINQ) - August 28th, 2012
- SQL for .NET Programmers–The Book - July 29th, 2013
- SQL For Programmers - Finding a String - December 4th, 2013
- SQL WHILE - SQL For Programmers - February 12th, 2014