SQL – Transactions

food-frveg-017

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.

Related Post

3 Responses to “SQL – Transactions”

  • David Shirey:

    Dumb question. I am new to SQL and don’t know the RaisError function. Doesn’t it have to be checked by the program calling the stored procedure also? Or does it automatically make itself known to the calling program? And if so is that a catastrophic awareness or polite? My SQL reference books don’t mention it.

  • Dave:

    In the context of the discussion above, the calling program would need to deal with the error. In the case of .NET, this would be an exception that would be raised. If the .NET programmer did not handle the exception, .NET would.

  • Sabih:

    Nice!
    This is very good and informative post for SQL.

Leave a Reply

Comment Policy:

  • You must verify your comment by responding to the automated email that is sent to your email address. Unverified comments will never show.Leave a good comment that adds to the conversation and I'll leave your link in.
  • Leave me pure spam and I'll delete it.
  • Leave a general comment and I'll remove the link but keep the comment.

Notify me of followup comments via e-mail

Bear