.NET Answers

Ruth ask,

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

You might, for example have the number be the line number where the error occurred.

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

You should probably make the message have more information.  Also, RaisError only has one E.  I guess the SQL guys can’t spell any better than I can.

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 Related Items:

Brand New Atrend 12sql 12" Single Sealed Car Subwoofer Enclosure with Heavy Duty 3/4" High Density Mdf (1.0 Cubic Feet Air Volume)BRAND NEW ATREND 12SQL 12" SINGLE SEALED CAR SUBWOOFER ENCLOSURE WITH HEAVY DUTY 3/4" HIGH DENSITY MDF (1.0 CUBIC FEET AIR VOLUME) Features: Single ... Read More >

Learning Implementing SQL 7.0 Level 4 Keystone Learning Systems KLS

Custom Accessories CU041120 Roll Back Window Sun ShadeCustom Accessories 41120 Rollback Sunshade - 14 x 19 (1 Each)

If you're new here, you may want to subscribe to my RSS feed. Thanks for visiting!

Related Post

• SQL SELECT CASE Instead of IIF
• SQL For Programmers – New Question
• SQL – Filtering WHERE condition on two rows
• Random in SQL – SQL For Programmers
• SQL CURSOR Performance – SQL For Programmers

Bookmark to:

Hide Sites

2 Responses to “SQL – Transactions”