August 21, 2021 at 12:00 am
Comments posted to this topic are about the item A Lack of Error Handling
August 21, 2021 at 4:50 am
Just like index maintenance and a couple of other things, it's better to NOT do something than it is to do it wrong and, thanks to the poor examples in official documentation, a lot of people simply do it wrong.
I'm also dead set against using Try/Catch just to say it's in the stored procedure (for example). If the normal built-in error handling will suffice, why is there a need to rebuild that wheel in code?
I HAVE used Try/Catch to great benefit in some things (like sending an email and then "Continuing" backup loops if one backup happens to fail). I also do a fair bit of logging in my procs, if needed.
And that's the key... "If Needed" and "It Depends".
What's really troublesome for me personally is all the folks that write articles on how to use Try/Catch and get it as wrong as the MS documentation did but even the good articles on the subject miss something that is just as or maybe more important and certainly can be used to seriously augment the presence of good Try/Catch code. That something is how to properly use SET XACT_ABORT ON.
Even Brent didn't mention it in his article. Yep... I know his intent was to write about Try/Catch but my personal feeling is that no Try/Catch article and, certainly, no article that talks about error handling is complete without the why, when, and how to use SET XACT_ABORT ON.
Spoiler: Since I've never seen it cause any harm, my recommendation is of when to use it is "always unless you know a specific reason not to for the given stored procedure".
--Jeff Moden
Change is inevitable... Change for the better is not.
August 21, 2021 at 1:15 pm
I always found that the biggest error hazards, especially when receiving data from other software, were data type/conversion problems. When data was coming from a front-end I always preferred to received it in varchar or nvarchar format and then do my own data validation/conversion using built-in SQL functions, then throw the errors back to the application.
In one position I was the second DBA/SQL Developer to come onboard for several large application projects at the same time, and the existing code at that point was pretty much devoid of error-handling due to a single person attempting to handle the demands for code to be produced, which led to front-end folks attempting to help with the SQL. Most front-end developers knew very little about the functions to do validation.
Proper data-type validation could solve almost all of the internal database error issues.
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
August 23, 2021 at 7:12 am
Stored Procedures? How nice. A memory of a bygone era.
Our developers almost exclusively use Entity Framework and I'm sure (and I hope) that this has error-handling in it for them.
I write SPs myself to load tables with data so that reports can be sent quickly from SSRS. The SPs are run by a job sometime in the early morning, when a 2-3 minute SP, which may possibly block writers on important tables, will cause the fewest problems. I don't use error-catching in these SPs per se. If the job fails, I will see the failed-job notification first thing in the morning.
I do, however, believe in commenting my code well and using PRINT statements liberally. They are very useful in long multi-statement queries. While testing it, they help me quickly find the block of code generating the problem and, after it has run successfully, I save the resulting text at the end of the query within a comment. This is helpful for me or others when trying to make sense of what I did, why I did it and what result I got after I had run the script.
August 23, 2021 at 1:19 pm
I'd love to know how many users in this forum are exclusively Entity Framework developers and believe that stored procedure are "a memory of a bygone era."?? hahah
August 23, 2021 at 1:24 pm
For us, stored procedures aren't a by-gone era. Instead, we embrace them. We tried letting the ORM (whatever flavor it was over time) do some of the complicated things and it made a pretty big mess of things, especially (but not limited to) when it came to datatype matching which has a huge impact on performance. I haven't been that kind of Developer for a very long time but, rumor has it that ORM does have the capability to use the correct datatypes but it seems that a lot of people either aren't aware of the functionality or they just don't care.
We also have some complicated stuff we need to do to display on many screens in the GUI. The application is under constant "improvement". One of the things that we embrace is that it's a whole lot easier to modify a stored procedure, test it, and run it up the chain to production (which can be done without an outage) than it is to juggle multiples of those kinds of changes in multiple branches of GUI code that needs to be compiled and deployed (which usually requires an outage) being careful of what's in a branch or not. It also frequently allows us to fix problems and add functionality much more quickly and we usually don't have to manage any release order or worry about branching, etc.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 23, 2021 at 1:26 pm
It is better to re-phrase that as: of the people on this forum, how many are DBAs to systems that their developers prefer to use Entity Framework on rather than SPs? Our developers far prefer EF to SPs on the grounds of the speed of development. It is now company policy, despite the protestations of the DBAs.
The developers on this forum in the past have defended the use EF (as long as it is done well) while also saying that they use SPs too. My guess is that the standard full-stack .Net developer is not on this site and has a hard time keeping up with all of the new technologies, especially all of the JavaScript libraries. I'm sure that they are good with their error-handling too.
August 23, 2021 at 1:27 pm
I'd love to know how many users in this forum are exclusively Entity Framework developers and believe that stored procedure are "a memory of a bygone era."?? hahah
That's one helluva "first post", Ollie. Welcome aboard! You're gonna fit right in, IMHO. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
August 23, 2021 at 3:37 pm
.....
We also have some complicated stuff we need to do to display on many screens in the GUI. The application is under constant "improvement". One of the things that we embrace is that it's a whole lot easier to modify a stored procedure, test it, and run it up the chain to production (which can be done without an outage) than it is to juggle multiples of those kinds of changes in multiple branches of GUI code that needs to be compiled and deployed (which usually requires an outage) being careful of what's in a branch or not. It also frequently allows us to fix problems and add functionality much more quickly and we usually don't have to manage any release order or worry about branching, etc.
+1
You're preaching to the choir here 🙂
BTW I cannot comment on Entity Framework as I do not use it.
Far away is close at hand in the images of elsewhere.
Anon.
August 23, 2021 at 4:01 pm
I generally never put error handling into a stored procedure unless I want to take some specific action inside the stored procedure for that error. If an error is raised when the stored procedure is called it will pass the error back to the calling program which will handle the error.
August 23, 2021 at 4:48 pm
The whole point of error handling is to actually handle the error in some way. Where that makes sense to do depends what the over all set up looks like. Catching an exception just to rethrow it or catching an exception just to suppress it usually doesn't make much sense. And in a lot of cases simply letting the SQL Error bubble up to the calling application is exactly what we want, whether that's some web app, SQL Agent etc....
August 24, 2021 at 12:01 am
Our company uses EF and stored procedures with error handling. Our standard is to require Try/Catch blocks in all stored procedures. While I agree with Jeff Moden's point in principle that error handling should be included only when default handling is inadequate, making the decision as to whether default handling is good enough isn't easy. Lack of error handling too often allowed unexpected results or awkward handling of unhandled exceptions bubbling up to the UI. This is with automated testing which we have used for all backend views and procedures for about 10 years now. Automated tests only cover so many scenarios (at an affordable cost) and you're guaranteed that given enough time, end-users will find the rest 🙂 Our team consensus was that it was more conservative to always include error handling than leave it out so its inclusion is now a standard.
I do agree that error handling code isn't easy - even with experience. This is particularly true with respect to the proper use of the SET XACT_ABORT statement. It is easy to get it wrong and end up with transaction count errors and various other problems that mask the underlying error that initiated the code block in the first place. Debugging error handling errors ... isn't fun and it doesn't feel very productive. Still, by always including error handling, our team did eventually build up a clearer understanding of which error handling patterns are required in what situations. Even still, we don't always get it right.
Also, to get around the challenges of getting good error handling in (ORM) generated code to support EF, we wrote our own generator. This is easier than you may think at first including being able to re-generate the sprocs while preserving custom code blocks as the data model changes.
Interesting article and comments. I appreciate this community.
August 24, 2021 at 7:20 am
Our company uses EF and stored procedures with error handling. Our standard is to require Try/Catch blocks in all stored procedures. While I agree with Jeff Moden's point in principle that error handling should be included only when default handling is inadequate, making the decision as to whether default handling is good enough isn't easy. Lack of error handling too often allowed unexpected results or awkward handling of unhandled exceptions bubbling up to the UI. This is with automated testing which we have used for all backend views and procedures for about 10 years now. Automated tests only cover so many scenarios (at an affordable cost) and you're guaranteed that given enough time, end-users will find the rest 🙂 Our team consensus was that it was more conservative to always include error handling than leave it out so its inclusion is now a standard.
I do agree that error handling code isn't easy - even with experience. This is particularly true with respect to the proper use of the SET XACT_ABORT statement. It is easy to get it wrong and end up with transaction count errors and various other problems that mask the underlying error that initiated the code block in the first place. Debugging error handling errors ... isn't fun and it doesn't feel very productive. Still, by always including error handling, our team did eventually build up a clearer understanding of which error handling patterns are required in what situations. Even still, we don't always get it right.
Also, to get around the challenges of getting good error handling in (ORM) generated code to support EF, we wrote our own generator. This is easier than you may think at first including being able to re-generate the sprocs while preserving custom code blocks as the data model changes.
Interesting article and comments. I appreciate this community.
I totally agree that if there's any question, folks should write error handling. And, rather than have two standards that someone might get wrong, I do agree with having a standard that requires proper error handling in each proc including some standard templates and examples to help people out.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply