asic error handling in SQL Server’s programming language, Transact-SQL, is straightforward. But when you nest calls to stored procedures, and the procedures have SQL transactions, error handling becomes much more complex. In this article I’ll show you some tried-and-true models for how to handle errors in nested stored procedures with transactions. Over the past year I’ve worked on two projects that needed a strategy for handling errors in stored procedures where the procedures used transactions. My task was to come up with a way to gracefully exit from the stored procedures when non-fatal errors were detected so I could roll back the transaction. I’ll present the result of that work in the two models you’ll learn about in this article.
So how do you handle errors in your code when an error occurs? Unless it encounters a broken connection, SQL Server will return an error to the client application. When you work directly with your own client or middle-tier code, you have much more control over how you handle errors. So you could just issue all your queries to SQL Server discretely from your client code and let SQL Server errors throw you into your error-catching logic, thereby keeping all your error handling in your own calling code. However, encapsulating database-oriented code in SQL Server stored procedures offers a more efficient and elegant solution. In those cases, you need to consider what to do when SQL Server errors occur.
Let’s look first at some general features of error handling.
Transact-SQL Error Handling
Transact-SQL error handling techniques are simple, but SQL Server’s error-returning behavior can seem confusing and occasionally inconsistent. You can trap some errors in Transact-SQL code, but other errors are fatal to a batch or transaction. When SQL Server encounters a non-fatal error trying to execute a command, the @@ERROR system function captures the error message. If the error is fatal, you cannot catch the error in Transact-SQL at all; you’ll have to rely on your client code’s catch logic.
If a trappable error occurs, the @@ERROR function will have a value greater than 0. SQL Server resets the @@ERROR value after every successful command, so you must immediately capture the @@ERROR value. Most of the time, you’ll want to test for changes in @@ERROR right after any INSERT, UPDATE, or DELETE statement. I prefer to capture the value of @@ERROR into a variable immediately, so I can use it later, as in the following snippet:
DECLARE @Error int ... UPDATE ... SET @Error = @@ERROR IF @Error > 0 ...
Because SQL Server resets the @@ERROR with the next successful command, when the IF statement in the code snippet successfully executes, SQL Server will reset @@ERROR back to 0. This can cause a problem if you’re also interested in getting the row count of a command, because most commands will also reset the @@ROWCOUNT system. You can capture them both simultaneously using the SELECT statement as shown in the following snippet:
DECLARE @Error int, @Rowcount int ... UPDATE ... SELECT @Error = @@ERROR ,@Rowcount = @@ROWCOUNT IF @Error > 0 ... IF @Rowcount = 0 ...
You can also capture @@ERROR to test for SELECT errors, with some limitations. For example, you can trap an error if at runtime some object (table or view) referenced by the SELECT is missing (Transact-SQL error message 208.) However, syntax errors in the SELECT command, or any other error that causes compilation of a batch to stop, are fatal and cannot be trapped.
|
It’s more useful to capture @@ERROR after INSERT, UPDATE, and DELETE statements because, by default, constraint violations are not fatal. Foreign key and check constraints will not be fatal (meaning they will not abort the batch or transaction) unless SET XACT_ABORT is ON (see the section on XACT_ABORT below.)
The number of possible error messages is very large; over 3,800 error messages are stored in the master database’s sysmessages system table (some are actually templates). Unfortunately, only a small number of the error messages are documented in Books Online; you can often get more complete explanations of errors in the Knowledge Base.
You can use the RAISERROR statement to generate your own errors in Transact-SQL code. You can also define your own error messages, starting with number 50001, using the system stored procedure sp_addmessage, which will add a message to the sysmessages table. You can then reference the error message in the RAISERROR statement. However, most developers prefer to insert a string message into the RAISERROR statement, because adding custom messages to the sysmessages table creates an additional dependency of your database on a table outside the database, thereby making it less portable.
Batches and Stored Procedures
SQL Server compiles and executes its code in batches of commands. When you work with SQL Server scripts, you use the GO statement for separating batches (it is not really an executed command.) Every stored procedure, trigger, and user-defined function can each consist of only one batch. SQL Server has some important restrictions on batches. For example, you must make the CREATE PROCEDURE the first statement in a batch, so you can create only one procedure per batch.
It’s possible that an SQL Server error may abort the current batch (stored procedure, trigger, or function) but not abort a calling batch. In this article, I will focus primarily on stored procedures, with some remarks about triggers in the context of transactions.
You should consider two major points when you work with SQL Server stored procedures and errors:
- Does an SQL Server error abort a called batch or stored procedure?
- Does the error abort a set of nested (called) stored procedures?
Using Transactions
If you encapsulate any of your operations in database transactions, some errors will abort a transaction while others will not. When working with transactions, consider the following questions:
- Does the error abort the transaction?
- What type of transaction is it?
- Is the XACT_ABORT setting on?
When SQL Server aborts a transaction, it also aborts the current and calling batches or stored procedures. SQL Server has three types of transactions: Autocommit, Explicit, and Implicit.
Autocommit: All data-modification statements such as INSERT, UPDATE, and DELETE occur in a transaction. If you do not explicitly declare a transaction, or if you use an implicit transaction, SQL Server automatically uses a transaction for those commands. That is the autocommit mode. SQL Server ensures these data-modification commands either completely succeed or completely fail. If you update a million rows, and SQL Server cannot complete the update, it will not leave the database only partially updated. This keeps the database in a consistent state and assures the atomicity of the transaction. That is, all the steps of a transaction as a group must complete, or everything gets rolled back.
|
Explicit: You can explicitly begin a Transact-SQL transaction with BEGIN TRANSACTION, optionally label it, and end the transaction with either COMMIT TRANSACTION or ROLLBACK TRANSACTION?but not both. A COMMIT statement instructs SQL Server to commit your changes, while a ROLLBACK statement results in all changes being removed. SQL Server can do this because it first writes all data changes to the transaction log before it changes any actual database data. If SQL Server needs to restore any data to its original state because of an error or a ROLLBACK, it can recover that data from the transaction log.
When you explicitly begin a transaction, the @@TRANCOUNT system function count increases from 0 to 1; when you COMMIT, the count decreases by one; when you ROLLBACK, the count is reduced to 0. As you see, the behavior of COMMIT and ROLLBACK is not symmetric. If you nest transactions, COMMIT always decreases the nesting level by one, as you can see illustrated in Figure 1. The ROLLBACK command, on the other hand, rolls back the entire transaction, illustrated in Figure 2. This asymmetry between COMMIT and ROLLBACK is the key to handling errors in nested transactions.
![]() |
The Single-Level Model The basic strategy for the single-level model is to start by declaring a local variable to record whether this procedure should begin a transaction or not. If the transaction count is 0 when the transaction starts, the procedure issues a BEGIN TRANSACTION. If you call another stored procedure, you should capture both the return value of the stored procedure and the value of @@ERROR. If the return value from the called procedure is -1, or if @@ERROR was greater than 0, the procedure assumes that an error has occurred that requires us to stop further processing and exit through an error path. If the return value from the called procedure is -1, the called procedure has already raised an error so there’s no need to raise one again. However, if the stored procedure call failed, or there was a non-trappable error in the called procedure, you should raise an error and report it to the caller so that you’ll know what procedure actually failed.
If the stored procedure has its own critical event, such as an INSERT, UPDATE, or DELETE, then you should test that command. Again, capture the value of @@ERROR; if it is greater than zero, the procedure should abort its processing. In this case you should raise an error indicating where the problem occurred, and exit through the error path. In the procedure’s error exit path, you test whether this procedure began a transaction. If it did, then the procedure issues a ROLLBACK, In either case the procedure should RETURN a -1 to tell a calling procedure that it should also exit through its error exit path. When no errors occur, the procedure should exit through a normal exit path; if it started a transaction, the procedure should COMMIT and return a 0. If it did not start a transaction, there’s no need to issue a COMMIT. Listing 1 contains the outline of a stored procedure using the single-level model. It shows the code for the outermost procedure, but the same code works at any level. If a procedure is at the innermost level of a set of nested procedures, you can remove the code that traps for calling a stored procedure. If a procedure does not begin a transaction, set the @LocalTran flag to 0. The procedure will never execute either a COMMIT or a ROLLBACK, but will still trap for errors calling other stored procedures and exit out its error path (the ErrExit label) if it detects a failure from called procedures. I’ve used this model in production a couple times now, and it works well. When SQL Server returns errors from low in the procedure nesting, the error messages help to easily pinpoint the location. The Multi-Level Model Both models only roll back a transaction at the outermost level. While the multi-level model explicitly begins a transaction, it makes sure that every procedure below the outermost one issues a COMMIT rather than a ROLLBACK, so the @@TRANCOUNT level is properly decremented even if the procedures are all exiting through their error path. In this model, you do not need to keep track of whether this procedure began a transaction. Instead, just issue the BEGIN TRANSACTION. The error handling for calling other stored procedures and issuing critical commands remains the same. If the procedure exits via its normal exit path, it should just issue a COMMIT and return a 0. But if the procedure exits out its error path (through its ErrExit label), there are two options. If the value of @@TRANCOUNT is greater than 1, this procedure did not initiate the transaction, so it should just COMMIT and return a -1. If @@TRANCOUNT is exactly 1, this procedure did initiate the transaction, so it issues a ROLLBACK and returns -1. Listing 2 shows sample code using this strategy. Again, if you are not calling other procedures, you can just remove the related code. However, if you are not using a transaction in this procedure, you’ll also need to remove the COMMIT and ROLLBACK conditions from the code. Comparing the Two Models The advantage of the multi-level approach is that you can use code that is somewhat simpler. The advantage of the single-level approach is that you can easily turn the transaction handling on or off without removing or commenting out lines of code. I’ve used the single-level model in two applications and it is working fine. Whichever model you choose, you’ll have a solid approach to handling Transact-SQL errors in nested procedures that use transactions. devx-admin
Share the Post:
![]() ![]() How ChatGPT is Changing the Game
Johannah Lopez
October 3, 2023
The AI-powered tool ChatGPT has taken the computing world by storm, receiving high praise from experts like Brex design lead, Pietro Schirano. Developed by OpenAI, ![]() ![]() Cybersecurity Battles: Lapsus$ Era Unfolds
Lila Anderson
October 3, 2023
In 2023, the cybersecurity field faces significant challenges due to the continuous transformation of threats and the increasing abilities of hackers. A prime example of ![]() ![]() Inside Apple’s AI Expansion Plans
Jordan Williams
October 3, 2023
Rather than following the widespread pattern of job cuts in the tech sector, Apple’s CEO Tim Cook disclosed plans to increase the company’s UK workforce. ![]() ![]() AI Stocks to Watch
Noah Nguyen
October 3, 2023
As investor interest in artificial intelligence (AI) grows, many companies are highlighting their AI product plans. However, discovering AI stocks that already generate revenue from ![]() ![]() Web Application Supply Chain Security
Grace Phillips
October 2, 2023
Today’s web applications depend on a wide array of third-party components and open-source tools to function effectively. This reliance on external resources poses significant security ![]() ![]() Thrilling Battle: Germany Versus Huawei
Lila Anderson
October 2, 2023
The German interior ministry has put forward suggestions that would oblige telecommunications operators to decrease their reliance on equipment manufactured by Chinese firms Huawei and ![]() ![]() How ChatGPT is Changing the Game
Johannah Lopez
October 3, 2023
The AI-powered tool ChatGPT has taken the computing world by storm, receiving high praise from experts like Brex design lead, Pietro Schirano. Developed by OpenAI, ChatGPT is known for its ![]() ![]() Cybersecurity Battles: Lapsus$ Era Unfolds
Lila Anderson
October 3, 2023
In 2023, the cybersecurity field faces significant challenges due to the continuous transformation of threats and the increasing abilities of hackers. A prime example of this is the group of ![]() ![]() Inside Apple’s AI Expansion Plans
Jordan Williams
October 3, 2023
Rather than following the widespread pattern of job cuts in the tech sector, Apple’s CEO Tim Cook disclosed plans to increase the company’s UK workforce. The main area of focus ![]() ![]() AI Stocks to Watch
Noah Nguyen
October 3, 2023
As investor interest in artificial intelligence (AI) grows, many companies are highlighting their AI product plans. However, discovering AI stocks that already generate revenue from generative AI, such as OpenAI, ![]() ![]() Web Application Supply Chain Security
Grace Phillips
October 2, 2023
Today’s web applications depend on a wide array of third-party components and open-source tools to function effectively. This reliance on external resources poses significant security risks, as malicious actors can ![]() ![]() Thrilling Battle: Germany Versus Huawei
Lila Anderson
October 2, 2023
The German interior ministry has put forward suggestions that would oblige telecommunications operators to decrease their reliance on equipment manufactured by Chinese firms Huawei and ZTE. This development comes after ![]() ![]() The iPhone 15’s Secrets and Surprises
Jordan Williams
October 2, 2023
As we dive into the most frequently asked questions and intriguing features, let us reiterate that the iPhone 15 brings substantial advancements in technology and design compared to its predecessors. ![]() ![]() iPhone 15 Pro Max: Overcoming Chip Setbacks
Noah Nguyen
October 2, 2023
Apple recently faced a significant challenge in the development of a key component for its latest iPhone series, the iPhone 15 Pro Max, which was unveiled just a week ago. ![]() ![]() iPhone 15: Performance, Camera, Battery
Johannah Lopez
October 2, 2023
Apple’s highly anticipated iPhone 15 has finally hit the market, sending ripples of excitement across the tech industry. For those considering upgrading to this new model, three essential features come ![]() ![]() Electric Vehicle Battery Breakthrough
Grace Phillips
October 2, 2023
The prices of lithium-ion batteries have seen a considerable reduction, with the cost per kilowatt-hour dipping under $100 for the first occasion in two years, as reported by energy analytics ![]() ![]() Virginia’s Clean Economy Act Soars Ahead
Lila Anderson
October 2, 2023
Virginia has made significant strides towards achieving its short-term carbon-free objectives as outlined in the Clean Economy Act of 2020. Currently, about 44,000 megawatts (MW) of wind, solar, and energy ![]() ![]() Innovative Energy Storage Solutions
Jordan Williams
October 2, 2023
The Department of Energy recently revealed a significant investment of $325 million in advanced battery technologies to store excess renewable energy produced by solar and wind sources. This funding will ![]() ![]() Revolutionizing India’s Tech Sector with Renesas
Noah Nguyen
October 2, 2023
Tushar Sharma, a semiconductor engineer at Renesas Electronics, met with Indian Prime Minister Narendra Modi to discuss the company’s support for India’s “Make in India” initiative. This initiative focuses on ![]() ![]() Thrilling East Windsor Mixed-Use Development
Johannah Lopez
October 2, 2023
Real estate developer James Cormier, in collaboration with a partnership, has purchased 137 acres of land in Connecticut for $1.15 million with the intention of constructing residential and commercial buildings. ![]() ![]() Top Software Development Companies in USA
Johannah Lopez
October 1, 2023
Navigating the tech landscape to find the right partner is crucial yet challenging. This article offers a comparative glimpse into the top software development companies in the USA. Through a ![]() ![]() Top Software Development Companies
Noah Nguyen
September 30, 2023
Looking for the best in software development? Our list of Top Software Development Companies is your gateway to finding the right tech partner. Dive in and explore the leaders in ![]() ![]() Top Web Development Companies in India
Jordan Williams
September 30, 2023
In the digital race, the right web development partner is your winning edge. Dive into our curated list of top web development companies in India, and kickstart your journey to ![]() ![]() Top Web Development Companies in USA
Johannah Lopez
September 30, 2023
Looking for the best web development companies in the USA? We’ve got you covered! Check out our top 10 picks to find the right partner for your online project. Your ![]() ![]() Inside Michigan’s Clean Energy Revolution
Grace Phillips
September 29, 2023
Democratic state legislators in Michigan continue to discuss and debate clean energy legislation in the hopes of establishing a comprehensive clean energy strategy for the state. A Senate committee meeting ![]() ![]() European Chips Act: What is it?
Lila Anderson
September 29, 2023
In response to the intensifying worldwide technology competition, Europe has unveiled the long-awaited European Chips Act. This daring legislative proposal aims to fortify Europe’s semiconductor supply chain and enhance its ![]() ![]() You Should Use Low-Code Platforms for Apps
Jordan Williams
September 29, 2023
As the demand for rapid software development increases, low-code platforms have emerged as a popular choice among developers for their ability to build applications with minimal coding. These platforms not ![]() ![]() Five Powerful Strategies to Bolster Your Cybersecurity
Noah Nguyen
September 29, 2023
In today’s increasingly digital landscape, businesses of all sizes must prioritize cyber security measures to defend against potential dangers. Cyber security professionals suggest five simple technological strategies to help companies ![]() ![]() Tech Layoffs Are Getting Worse Globally
Jordan Williams
September 29, 2023
Since the start of 2023, the global technology sector has experienced a significant rise in layoffs, with over 236,000 workers being let go by 1,019 tech firms, as per data ![]() ![]() Huawei Dazzles with Electric Vehicles and Wireless Earbuds
Johannah Lopez
September 29, 2023
During a prominent unveiling event, Huawei, the Chinese telecommunications powerhouse, kept quiet about its enigmatic new 5G phone and alleged cutting-edge chip development. Instead, Huawei astounded the audience by presenting ![]() ![]() Digital Banking Needs Cybersecurity
Johannah Lopez
September 29, 2023
The banking, financial, and insurance (BFSI) sectors are pioneers in digital transformation, using web applications and application programming interfaces (APIs) to provide seamless services to customers around the world. Rising |