David Tovee asked a great question in yesterday’s Mastering Query Tuning class. He asked his fellow students, “How many of you actually use TRY/CATCH?”
I turned it into a Twitter poll because I wanted to check a wider audience:
When the developers at my company write new queries, they use TRY/CATCH and exception handling:
— Brent Ozar (@BrentO) August 19, 2021
The poll results mirror the experience I see with clients: the vast majority of T-SQL code doesn’t have any error handling whatsoever.
When I do run across error handling in client queries, I get all excited and I ask about it. The answer is usually, “Oh yeah, that was implemented years and years ago by a developer who used to work here, and they left.” It’s often legacy leftovers from when TRY/CATCH was first introduced, and then…never revisited again, and rarely included with new queries.
If you’d like to get started with error handling, check out these resources:
- Error and Transaction Handling in SQL Server – Part 1 – Jumpstart Error Handling by Erland Sommarskog
- Part 2 – Commands and Mechanisms
- Part 3 – Implementation
- SQL Server Error Handling Gotchas by Niels Berglund
Right about here is the part of the blog post where you might expect a finger-wagging lecture from me about how queries should always handle errors, but, uh…I don’t do error handling either. I know, right? But the thing is, I can’t remember the last time a client hired me to write a new query from scratch. If I never do something, then I won’t be good at it, and I certainly can’t lecture you to be good at it, either.
If you DO put error handling into your queries, though, take a moment to hug yourself. You rock, and you’re unusual amongst the wider audience. The world needs more people like you.
46 Comments. Leave new
Errors in T-SQL gracefully propagate to calling application.
IMO there are too few reason one would use TRY/CATCH in each and every stored procedure or query like (in order of frequency):
1. swallow the error
2. log the error
3. handle the error by executing other meaningful actions
I’ve frequently done 1. and probably 2. but never seen 3. implemented in T-SQL because usually 3. is implemented at the application layer (e.g. retry transaction on concurrency violation, increase timeout, etc.)
How many check for a deadlocks? Rearly/Never 99.9%
Depends on what you are trying to achieve but it’s often simpler and more elegant to handle exceptions from the app. Also bad exception handling can be worse than no exception handling.
First time I feel qualified to comment on something technical on this blog!
I would advise thinking long and had before using try..catch anywhere both in your apps and in T-SQL. Some people want to put an error handler in every function. Back in the 90s people put ‘on error resume next’ all over their VB apps. It was a bad idea then and it’s still a bad idea now!
If something goes wrong I want it to be surfaced immediately, not hidden. Do your catch blocks all call out to a reporting/logging infrastructure? Does it alert? Does anyone check it?
I start from the principle of no try… catch anywhere in a system, and then strategically add it where I have to, in places, for example, where I have no control of the input, such as the processing of an external piece of JSON or something like that.
Maybe there are valid cases like this in T-SQL like kicking off remote jobs or reading remote files but I’d suspect they are few and far between. Much better to let T-SQL errors bubble up to a point where someone has a chance to see them and fix them.
I whole heartedly agree. If data comes in that doesn’t fit the foreseen pattern, I want to know immediately and I don’t want the code to continue until the data is correct
In most cases I implement try/catch. Where I don’t, on occasion, I have added try/catch later. It is a great tool to capture more than just the error message. This captured data provides the necessary information improve the application and/or stored procedure rather than making a guess from the error message alone. In addition, it provides information that can be used for test cases to verify any fixes made actually fix the problem.
There is one terrible limitation of try/catch that limits error information in some cases. Try/catch, by design, only returns the outer most error. In most cases, this is good enough, but for some, the outer most error is useless. For example, a database backup, the outer most error will be “the backup terminated abnormally.” The inner errors will contain why it terminated abnormally. A Microsoft Connect case was opened, but Microsoft closed as by design, won’t fix.
Despite this limitation, I find try/catch invaluable.
A tool – which is not a replacement for correct error handling – is to log runtime errors in DB using Extended Events (XE) Logging. The logging can be filtered on specific apps or usernames (so that ad hoc queries with just wrong syntax is not logged).
When used in testing environments, it is an easy way to point out simple errors that otherwise might (silently) hit you (or the app developers) later in production. After each test run, the list of runtime errors from XE is inspected.
And again: it is not a replacement for proper error handling (!) – but is it cheap, fast and generic way to anticipate some runtime errors and raise quality.
The main place I see try-catch is when we have a transaction where we want all of the transaction to commit or none at all. Our app code handles transient sql errors and will retry with increasing wait periods or the data gets thrown back into a queue and gets retried later.
Where I work Im in charge of enforcing best practice standards. All stored procedures start from 1 of 3 templates depending on the desired behavior (non-dml proc, non nested trans, nested trans) and go from there. Im big on error handling (i started my career doing a lot of disgusting visual basic stuff where error handling is a “thing”). im am always looking to make that process more solid (which means Im going ot be reading the links provided). Thanks!
I know its specific to SQL Server and SSDT, but I wrote a code analysis rule that we integrate with our build server to check for try catch:
https://github.com/tcartwright/SqlServer.Rules/blob/master/docs/Design/SRD0009.md
https://github.com/tcartwright/SqlServer.Rules/blob/master/SqlServer.Rules/Design/WrapStatementsWithTryCatchRule.cs
I’ve come across far more intractable errors caused by erroneous error handling than I have from the errors themselves! Nested stored procedures are absolute a minefield for this.
SQL handles errors and passes them up anyway. Only place I want to see them is if there’s a specific scenario that needs to be caught and passed back to the application with a custom error code and message.
ROLLBACK TRANSACTION
THROW
anyone?
depends on the type of work the code is doing. I worked on tons of code where:
1: an error should not abort further processing,
2:row ids and data details were needed when errors occurred
3: alerts needed to be sent out when errors occurred
4: etc. etc.
None of the above requirements are met by allowing sql engine to default handle the errors.
Personally I love the try catch that does nothing with the error. A group had an issue for months where this reporting/roll up stored proc would sometimes “just disappear” in the middle of processing. They asked me to look at it and wouldn’t you know, it was all wrapped in a try catch with no error alerting.
If it is all my code (and thus I trust my higher-level handling), I use try-catch, capture the error message (and other info) and then use that with RAISERROR to report it up to the next level handler.
If I don’t control the higher-level caller, then I (virtually) always capture the error information (and related data) and record it to a logging table. Then, depending on the calling protocol, either return an error code or use RAISERROR to report the error up to the caller.
Too many times, if I initially failed to include a Try-Catch, I wound up adding it later to track down an obscure data-related problem.
@kevin, we actually have a system that has a lot of nested stored procedures. We came up with this mechanism to build stack traces for the stored procs. Which makes hunting down the actual issue MUCH easier.
https://gist.github.com/tcartwright/aea2d834652b7d0ee7afc6edf8ade396
nice!
Wow. OK, so it is a Friday night and I am four pints in…. but… Creating a stored procedure in the temp db?? What?
What if you switch it off and on again?
What if it’s SQL Azure?
And you’re nesting stored procs in order to track an error…
OK, more beer needed. That’s probably what it is.
Thats for example. Create the proc wherever you want. I tend to write my examples for tempdb so they disappear. 🙂
Heh… Not to worry, Kevin. It’s not the pints. 😀 I think they have the USE TempDB in their listing to keep from accidentally blowing someone’s code out of the water. They DO have instructions that the person deploying the proc should read and follow.
As for the all the nested stored procedures? Yeah… I’m going to need a couple of pints myself. 😀
This ^ lol. I started to use them everywhere without question as to whether they were really necessary, and initially they were normally fine, but then when something gets updated would get the absolute craziest errors trying to update the error handling.
@kevin, look at how ours works, its completely generic to your procs. The error handlers are ALWAYS the same.
IMHO, SQL Server does a great job with error handling all on it’s own and (usually) doesn’t need anything extra in the form of TRY/CATCH. I’ll also warn that a lot of people that do use Try/Catch screw things up royally because of (either directly or indirectly) the really poor examples the MS documentation has on the subject. Heh… it reminds me of index maintenance… it’s better to not do it at all than it is to do it wrong or half-way.
It’s also a bit ironic and even sad that a whole lot of people that bang on the Try/Catch drum don’t say a word about the importance and proper use of SET XACT_ABORT ON; .
You said what I was going to say – from cost/benefit standpoint SET XACT_ABORT ON is the way to go.
+1000! But, don’t tell Microsoft that it’s a good idea or they’ll make it non-optional like they did with some other “normally good” options that put the screws to me big time because they haven’t fixed the SET IDENTITY INSERT problem cause full load sorts in TempDB.
@kevin: Every single error handler looks like this:
— error catch variables
DECLARE @ErrorMessage NVARCHAR(4000),
@ErrorSeverity INT,
@ErrorState INT = 0,
@proc_name sysname = CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME(@@PROCID)), ‘.’, QUOTENAME(OBJECT_NAME(@@PROCID)));
EXEC dbo.ErrorHandler @procName = @proc_name,
@ErrorMessage = @ErrorMessage OUTPUT,
@ErrorSeverity = @ErrorSeverity OUTPUT,
@ErrorState = @ErrorState OUTPUT;
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
SQL Server is very chatty and can blurt helpful “here’s how to hack my database” info in exceptions.
If you can’t count on applications to handle errors, it may be wise to implement error handling in stored procedures in order to return more constrained/edited messages that won’t help malicious (or even well-intentioned but naive) users do dangerous things.
I have two templates which I have distributed to our devs (to no almost avail). One is for very simple updates which has an IF check to make sure @@rowcount matches the expected count and a second template with TRY/CATCH logic and a space for verification queries to go. Both only commit if the validation comes back positive (otherwise they roll back and RAISERROR), and both use XACT_ABORT to immediately roll back when something goes horribly wrong. Exactly 1 dev uses those templates.
I’m very fortunate. I’ve been given the authority to reject any and all code that doesn’t follow our written standards. That IS Step 1… write the standards so that they’re easy to understand and almost completely “Gumby Proof”. Step 2 is to have management sign off on the standards and give you the power to enforce them “even if it’s going to make dinner late”. It needs to be mandated to be and “official part of everyone’s job” all the time. Step 3 is to publish them where everyone can easily get to them and copy any code needed. Step 4 is to take the time to enforce them to the “T”. If management tries to provide someone with a waiver, point them to the requirements of Step 3 and remind them that its a “official part of everyone’s job” and that includes management. They’ll also get used to that. 😀
There will be a lot of complaining and moaning until they “get it” and it becomes a “matter of rote” for them.
If pull requests are rejected because of no error handling they would learn it quickly 🙂
I use a combination of isolation level control, transaction control, and error handling, when I want something to happen other than just pass the error to the calling application. For example, in a proc that defragments indexes based on custom logic, which loops through the indexes that need some TLC, failing the proc and exiting the loop because of an error on an index is probably not appropriate. It should log the error, roll back any open transaction on it, and then try the next index. Similar for purge/archive processes related to data retention in transactional databases. Just because one row or set of rows fails (frequently due to deadlocks) is no reason to stop the process.
Are you using REORGANIZE anywhere in your custom index maintenance code? If you are, I seriously recommend that you reconsider. If you’re the “GSquared” from SQLServerCentral.com, PM me for more information.
I have the “distinction” of working with an application stack that is entirely database-bound. There are loads of triggers, periodic SQL Agent jobs, loads and loads of nested stored procedures, and very little in the way of a proper application (or reporting, for that matter) layer. Most of my peers on the app dev teams have very little in the way of T-SQL knowledge and our DBAs rose to their roles through sysadmin backgrounds.
My implementation model has been to
TRY-CATCHto log any exceptions beforeTHROWing them back to the caller with informative or instructive additional messages about the exception. This has helped a lot in tracking down issues: Now we have a proper stack to trace the exception through, and the end users encountering the problem have a fighting chance of fixing whatever nonsense (and it is, with little variation, nonsense) they’re trying to do.” the vast majority of T-SQL code doesn’t have any error handling whatsoever.”
This is of course one of the most telling indictments of our profession. The general level of understanding of basic programming best practices and principles for SQL developers is pathetically low. And in my experience many of my colleagues actually embraced the high level of manual maintenance and babysitting due to operational failures with their code. They considered it job insurance.
I think the statement you quoted is a misnomer. ALL T-SQL code has error handling built into it. In a lot of cases (most cases, IMHO), T-SQL does an adequate job and most certainly does a much better job than a lot of people’s attempts at TRY/CATCH. Yes, there are exceptions but not every proc needs a TRY/CATCH.
Now and to your very good point, I DO agree that front-end error handling and batch jobs need better logging. I just don’t believe that Try/Catch provides any kind of a panacea or even good utility (in T-SQL) there.
Again, that’s just my opinion.
It was rather complaining about developers. I was in a project when there was a problem with finding of SQL developer with git skills. Trust me it happens ofen than you think unfortunately 🙂
I’m all for try/catch, though I don’t yet understand how it could be beneficial to *always* use it when, in many cases, you would literally just be repeating the same information that was going to be communicated from SQL anyway and potentially with less detail.
+1000!
I did tons of maintenance and troubleshooting and therefore put error handling with the error code and text and if possible what record was being processed. Now unfortunately, as a generalist jack of all trades master of none, I don’t do much programming but even my reports have try/catch.
Despite me railing a bit against “using it for everything”, if you’ve done it right and it’s actually providing value above and beyond the normal error handling does, I’m all for it.
One of the uses I have is to display what I can of any Dynamic SQL that has failed. Yep… RAISERROR has an overall limit of something like 2k bytes but you can fit a fairly good chunk of code in that. It has saved us a whole lot of time.
I used a lot, and I mean a lot, of dynamic sql in my last job (a ton of data cleanup, ETL, etc.). I got in the habit of adding a parameter to all my dynamic sql procs, to support only return of the dynamically generated sql and not execute it.
This was trivial to do, and a huge help during development, testing, and debugging. And I would usually log the generated dynamic sql during every execution, for permanent auditing and reference.
If Brent says that the developer who uses TRY/CATCH rocks and can hug him self, does he imply that said developer should be put in a straight jacket? Asking for our friends, of course…
IMO the only time exceptions should be handled is if you can guarantee that you can restore the system to a sane state and continue.
This means that most of the time the database error should just be reported straight on, as there is normally nothing the query can do to resolve the issue. If it was deemed relevant by the client (or the parent stored proceedure), then is probably in a transaction box which will atomically succeed or rollback without any further cleanup needed.
How could a random ‘catch’ handle deadlock, out of disk space, timeout expired? within the database often the only thing that can be done is blindly re-execute the query that just failed, which is only of use in some deadlock scenarios.
Given that databases often don’t even have access to a good mechanism to ‘log’ such things, passing the exception back to the client for it to decide to retry or abort for this mechanism (or retry twice on timeout, then log) or other sophisticated policy.
I worked with numerous vendor provided front ends supporting interactive database updates by billers, lawyers, etc. Try giving such users back the default Sql Server error when there was a problem with their attempt to perform a database update. Won’t fly. You would need to hire an army of call center helpers to handle the calls. We had to trap errors so we could return domain specific and meaningul (to the users) error messages to help the users correct or understand their data and the error. No way would returning the Sql Server intrinsic error messages to users be acceptable. This is just one example…
Oh, yeah… I agree on something like that. As with all else in SQL Server, “It Depends”.
Almost all SQL code I have ever seen only needs SET XACT_ABORT ON; at the top of the procedure, in order to deal safely with almost any error that comes down the line, because then errors just feed straight back to the client app as proper exceptions, rather than horrible SELECT @error code which then needs to be dealt with separately.
On our busy OLTP database, 99% of errors are such that it isn’t really feasible to deal with anyway, such as deadlocks and constraint violations. You just pass them back up the chain and let the app deal with it.
The only time you really want proper error handling is in maintenance scripts, simple CRUD just doesn’t need it.
The worst code I’ve seen in TSQL is bad error handling that swallows exceptions or causes other errors, it really is better not to bother at all.
The reason it never caught on is that it’s simply not useful. “Queries” as we generally think of them — a single, read-only statement — can’t benefit from any exception handling. Most write operations, too, are single statements, and there’s not going to be any benefit there either, unless you’re trying to do something like log exceptions within the database. The vast majority of the remaining stuff is better handled with a transaction block and SET XACT_ABORT ON.
In any more “modern” architecture (i.e. something created in the last 10-15 years), the app tier is better suited than the database for handling the logging mentioned above, as well as any other exception handling. That means that SQL TRY-CATCH is useful for legacy stuff, and then maybe 0.01% of the time. So if you’re heavily using it, don’t give yourself a hug; update your resume instead and find a job with a newer stack to work on.