Home » SQL For Programmers » SQL – Transactions

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.

Like this Article? Subscribe to get every article sent to your email.

Related Post

  • Transaction Tracking Typed Datasets Using SqlTransactionTransaction Tracking Typed Datasets Using SqlTransaction I recently had the need to implement transaction tracking between two separate databases on two separate servers.Unfortunately, I could not be sure that DTC was implemented on […]
  • SQL IF/WHILE Blocks – SQL For ProgrammersSQL IF/WHILE Blocks – SQL For Programmers OK, all you programmer types.  Here's where SQL starts getting interesting.  You have the basic concepts of declaring variables, setting up stored procedures, and doing a basic insert, […]
  • Basic SQL Commands – SQL For ProgrammersBasic SQL Commands – SQL For Programmers Before we get into the specifics of the commands that can be used within a stored procedure, I think it would be helpful to review some of the more basic commands that we can use that […]
  • SQL CURSOR – SQL For ProgrammersSQL CURSOR – SQL For Programmers One of the things it took me a while to figure out was how to get a stored procedure to loop through data in a table and pull out specific data I needed.There are actually two parts […]
  • SQL For Programmers – New QuestionSQL For Programmers – New Question I got this question last week that I thought would be worth discussing on the blog. I would like to create a table that lists contributions by date, amount, and designation. In some […]

About Dave Bush

Dave Bush is a Full Stack ASP.NET developer. His commitment to quality through test driven development, vast knowledge of C#, HTML, CSS and JavaScript as well as his ability to mentor younger programmers and his passion for Agile/Scrum as defined by the Agile Manifesto and the Scrum Alliance will certainly be an asset to your organization.

  • 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.