Are you making this ROLLBACK mistake in your stored procedures?

If you read my post about the 6 rules you should know about SQL Server transactions, you should know there is one rule that can be a bit tricky. This rule centers around the idea of doing a ROLLBACK within a stored procedure.

The rule is this: When you leave a stored procedure, the number of open transactions must be the same as when you entered the stored procedure.

So if there was one open transaction when the flow of control entered the stored procedure, there needs to still be one open transaction when the flow of control leaves the stored procedure.

Things get a little weird when you want to open your own transaction from within a stored procedure.

As long as that internal transaction is committed successfully, things should be all good. In other words, you opened a new transaction in your procedure, and you made sure to close it at the end of the procedure. Great.

But things can go bad

What if things go bad and we need to ROLLBACK that internal transaction?

If we remember our other rule, a ROLLBACK will undo all open transactions, changing @@TRANCOUNT to zero regardless of what it was before.

So the question becomes this: “If we open a transaction within a stored procedure, but need to roll it back because of an error, how do we make sure only that ONE transaction is rolled back?”

In our example, if we create a transaction in our stored procedure, then issue a ROLLBACK, we would be undoing all transactions that might have been opened before we even entered the procedure.

We don’t want that to happen.

In this tutorial, we will go over what you need to do when you want to do transactional work in a stored procedure, but don’t want to affect any transactions that might have been created before the procedure was called.

The basic steps to this process are the following:

1. Figure out the work you want wrapped in a transaction within your stored procedure
2. Capture the number of transactions that were open when you entered the stored procedure
3. Give your internal transaction a name
4. If a ROLLBACK is issued, make sure it applies to only our named transaction. 
5. Create a savepoint for when a transaction was already opened before your procedure was called
6. Issue a COMMIT only if your internal transaction is the only open transaction. 

Also, everything in this tutorial is discussed in the following FREE Ebook:

FREE Ebook on SQL Server Transactions!

This FREE Ebook discusses everything you need to know about creating and using transactions within SQL Server. It will definitely be a great resource for you to keep and reference throughout your career as a data professional. Make sure you download it today!

The steps for rolling back in a stored procedure are a bit involved, so we’re going to take it slow.

1. Figure out the work you want wrapped in a transaction within your stored procedure

We need to get some setup work out of the way. We need a good example of when you might want to open a transaction within a stored procedure.

Let’s say you have a table to store information about your book collection. Here is the data in your Books table:

Cool. To make things consistent, you decide to add a check constraint to the Pages column that prevents the user from entering a Pages value that is zero or less:

Awesome. Now it’s not possible to enter an invalid Pages value.

Still with me?

Ok, let’s double check to make sure our constraint is working. Let’s try adding a book with zero pages:

So far, things are gravy.

Let’s create a stored procedure to outsource the work of adding a new book to our table. As arguments to the procedure, we pass all the column values. Here is what it looks like:

Nice. The stored procedure inserts the new row, then does a quick SELECT statement to show us the new row in the table. To test it out, let’s use it to add a new book to our collection:

Superb.

The transaction in your stored procedure

So I said the first step in this process is to identify what work you want wrapped in a transaction in your stored procedure.

In our stored procedure, let’s say we want the actual INSERT statement inside a transaction. Like this:

But remember, there is a chance the INSERT will fail. It would fail if the @pageCount is zero or less because of the check constraint. So we need to add code to do a ROLLBACK if things go south. We can use our handy @@ERROR function and an IF block to tell us if “things went south“:

If we enter the IF block because of an error, we do our rollback and exit the stored procedure via RETURN. If we never entered the IF block, we COMMIT like normal. 

Awesome, let’s try out our handy stored procedure with valid data:

And once again with invalid data:

Seems like things are working as expected. Let’s check the @@TRANCOUNT after that unsuccessful INSERT statement:

Good, we don’t have an open transaction sitting out there.

We’re on our way to making this a very solid procedure.

2. Capture the number of transactions that were open when you entered the stored procedure

The next thing we need to do is capture the number of transactions that were open when we entered the stored procedure.

We can accomplish that using a simple INT variable. At this time, don’t worry too much about why we’re capturing this number. We’ll need to use it later, and you’ll understand why we need it.

Here’s the code:

3. Give your internal transaction a name

It’s very easy to give your transactions a name.

When we do a ROLLBACK, we can specify the name of that transaction we want to roll back.

This is the crux of this solution. The way we can specify to “only roll back my transaction” is by giving “my” transaction a name.

It’s very easy to give your transaction a name. You literally just put the name you want to use directly next to the “BEGIN TRANSACTION“ statement, like this:

4. If a ROLLBACK is issued, make sure it applies to only our named transaction.

This is another easy one. If we need to issue a ROLLBACK, we want to make sure that only our internal transaction is affected. The way we do that is by referencing the transaction name in the ROLLBACK statement.

Just put the transaction name next to the ROLLBACK TRANSACTION statement (we need to include the word “TRANSACTION” when we need the ROLLBACK statement associated with a name) like this:

Excellent. Again, this is the heart of this solution. Through naming our transaction, we can be sure we are only rolling back what we intend to roll back. If we hit that ROLLBACK statement, it will only undo the code in the “newBookTransaction” transaction and not any other transactions that might have existed before the procedure was called.

Which leads us to our next point…

5. Create a savepoint for when a transaction was already opened before your procedure was called

This is where things get a little funky.

You just learned how to name a transaction. Now you’re going to learn what a savepoint is in a transaction.

A savepoint is used when you might need to issue a ROLLBACK in a transaction, but you don’t want to roll back the entire transaction. If you create a savepoint, you can roll back to that savepoint instead.

It’s almost like creating a checkpoint in the life of your transaction.

We need to think about what happens when we enter our stored procedure and are already in an open transaction. In that case, we really shouldn’t be opening another transaction within the procedure.

What we should do instead is create a savepoint within that outer transaction.

When we enter our procedure, if we can confirm there isn’t already an open transaction, we go ahead create one within our procedure. But if there is already an open transaction, we just use that transaction and identify our new work within that transaction by using a savepoint.

So the question becomes, “When we enter the stored procedure, how do we know if we’re already in an open transaction?”

Answer: By looking at the value of our @@TRANCOUNT variable created in step # 2.

So if our variable is zero, we know we aren’t already in an open transaction, so it’s safe to create one.

But if our variable is greater than zero, we know we don’t need to create a new transaction. Instead, we just create a savepoint within the already-existing transaction.

We can use a handy IF…ELSE block for that:

Going back to the ROLLBACK statement, remember how we associated the ROLLBACK statement with the transaction name (ROLLBACK TRANSACTION newBookTransaction).

This means if we need to do a ROLLBACK, we know we’ll roll back to the point where newBookTransaction was created. This will either be at the creation of the transaction if we created one in the procedure, or it will be at the savepoint we created in the event there is already an open transaction to begin with.

Pretty slick stuff.

6. Issue a COMMIT only if your internal transaction is the only open transaction.

Now we need to think about what to do with the COMMIT. If we created the transaction, it’s ok to issue the COMMIT statement. But if we didn’t create the transaction (meaning there was already an open transaction when we entered the stored procedure), we don’t want to commit it.

(We don’t want to handle the committing of a transaction that someone else opened. THEY likely want to control when the transaction is committed).

So we only want to COMMIT if we are sure there were no open transactions when we entered the procedure, meaning the only open transaction is the one we created in the procedure.

Again, how can we tell if a transaction was open when we entered the procedure?

Answer: We can use our handy @inputTranCount variable again:

Easy peasy.

Testing it out

Ok, now let’s see if all our efforts were worth it.

Let’s put a whole bunch of debug messages in the body of the stored procedure so we can see the flow of control when we do our tests:

Ok, so first up, let’s enter valid data outside of a transaction (meaning a transaction will be created within the stored procedure):

Excellent. We successfully opened and closed our transaction within the stored procedure.

Now let’s run it again with valid data, but put the call to our stored procedure inside a transaction:

Superb. We didn’t open a new transaction, and we didn’t accidentally COMMIT the already-existing transaction.

Next up, let’s enter invalid data outside of a transaction (meaning a transaction will be created within our stored procedure):

Fantastic. The error was caught and dealt with. Our internal transaction was rolled back successfully.

Now our last test, where we enter invalid data in a call to the stored procedure, all within an outer transaction:

Glorious. Our ROLLBACK didn’t effect the already-existing transaction. We know that because after the ROLLBACK TRANSACTION statement, the @@TRANCOUNT is still 1, which is what it was when we entered the stored procedure. 

I’d say this procedure is bullet proof.

Next Steps:

Leave a comment if you found this tutorial helpful!

Everything discussed in this tutorial can be found in the following FREE Ebook:

FREE Ebook on SQL Server Transactions!

This FREE Ebook discusses everything you need to know about creating and using transactions within SQL Server. It will definitely be a great resource for you to keep and reference throughout your career as a data professional. Make sure you download it today!

Do you understand these 6 rules about transactions? Check out the tutorial to find out!

If you are just starting out, and aren’t really sure what a transaction is or why we need them, take a look at the full introductory tutorial on SQL Server transactions:

SQL Server Transactions: An Introduction for Beginners

Also, you’ll want to understand how the ROLLBACK statement works within transactions:

SQL Server ROLLBACK: Everything you need to know

As we have seen, a common place you will likely need to create transactions is within stored procedures. Make sure you are familiar with them and how they can help you be a great SQL developer:

Stored Procedures: The Ultimate Guide for Beginners

Stored procedure with parameters: A guide for beginners

Finally, here is a link to the official Microsoft documentation on transactions: Transactions (Transact-SQL)

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, please leave a comment. Or better yet, send me an email!