Most T-SQL Queries Don’t Even Try to Handle Errors.

T-SQL
44 Comments

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:

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:

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.

Previous Post
You Probably Shouldn’t Index Your Temp Tables.
Next Post
[Video] Office Hours: Ask Me Anything About SQL Server

44 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.)

    Reply
  • How many check for a deadlocks? Rearly/Never 99.9%

    Reply
  • David Wiseman
    August 20, 2021 10:33 am

    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.

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

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

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

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

    Reply
  • Douglas Z Coats
    August 20, 2021 2:23 pm

    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!

    Reply
  • Tim Cartwright
    August 20, 2021 4:18 pm

    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

    Reply
  • 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?

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

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

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

    Reply
  • Tim Cartwright
    August 20, 2021 4:40 pm

    @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

    Reply
    • nice!

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

      Reply
      • Tim Cartwright
        August 20, 2021 8:33 pm

        Thats for example. Create the proc wherever you want. I tend to write my examples for tempdb so they disappear. 🙂

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

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

    Reply
  • Tim Cartwright
    August 20, 2021 4:50 pm

    @kevin, look at how ours works, its completely generic to your procs. The error handlers are ALWAYS the same.

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

    Reply
    • You said what I was going to say – from cost/benefit standpoint SET XACT_ABORT ON is the way to go.

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

        Reply
  • Tim Cartwright
    August 20, 2021 4:53 pm

    @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);

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

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

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

      Reply
    • If pull requests are rejected because of no error handling they would learn it quickly 🙂

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

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

      Reply
  • 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-CATCH to log any exceptions before THROWing 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.

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

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

      Reply
      • 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 🙂

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

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

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

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

      Reply
  • 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…

    Reply
  • Aart bluestoke
    August 22, 2021 11:17 pm

    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.

    Reply
  • 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…

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

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu