TRY/CATCH Doesn’t Always Work.
If you’re using TRY/CATCH to do exception handling in T-SQL, you need to be aware that there are a lot of things it doesn’t catch. Here’s a quick example.
Let’s set up two tables – bookmarks, and a process log to track whether our stored proc is working or not:
|
1 2 3 4 5 6 7 8 9 10 11 |
DROP TABLE IF EXISTS dbo.Bookmarks; DROP TABLE IF EXISTS dbo.ProcessLog; CREATE TABLE dbo.Bookmarks( URL VARCHAR(50)); GO CREATE TABLE dbo.ProcessLog( ProcessDate DATETIME, StatusMessage VARCHAR(50)); GO |
And create a simple stored procedure that adds a bookmark, and tracks whether it was successful:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE OR ALTER PROC dbo.AddBookmark @URL VARCHAR(50) AS BEGIN BEGIN TRY INSERT INTO dbo.Bookmarks VALUES (@URL); INSERT INTO dbo.ProcessLog VALUES (GETDATE(), 'It Worked'); END TRY BEGIN CATCH INSERT INTO dbo.ProcessLog VALUES (GETDATE(), 'It Failed'); END CATCH END GO |
When you execute the proc, it succeeds, and a row is written to ProcessLog:
But if someone adds a new column to our Bookmarks table:
|
1 |
ALTER TABLE dbo.Bookmarks ADD BookmarkedOn DATETIME; |
And we try to run our stored proc again, it fails:
Because the stored proc’s insert statement didn’t explicitly list the columns in the Bookmark table:
|
1 2 |
INSERT INTO dbo.Bookmarks VALUES (@URL); |
Okay, that’s bad code – but did the CATCH come into play? Check the table contents:
There’s no row in ProcessLog saying that the process failed! What happened? Wasn’t our CATCH supposed to insert a row there?
Catch only catches SOME errors.
Early errors aren’t caught, like errors when the query is being compiled. In this case, when SQL Server was building an execution plan for the stored procedure, SQL Server couldn’t build a valid execution plan because there’s no way for it to execute the insert. The compilation failed, which technically means the query wasn’t executed – even though to you and me and our app users, it was executed.
Low-priority errors aren’t caught, like under severity 10. Those are just considered informational messages.
High-priority errors aren’t caught, like severity 20 or higher. Those terminate the connection altogether.
Right about here is where you’re expecting me to give you a magic bullet that fixes these problems, but instead, I have to give you a monster amount of documentation. Check out the epic posts Error and Transaction Handling in SQL Server Part 1, Part 2, and Part 3 by Erland Sommarskog. They’re monster posts, and I have to be honest with you, dear reader, I haven’t ever read them cover to cover. This is one of those times where I’m glad I have a fake job, aka consultant, where I can just say, “If our stored procedure’s business logic is really that critical and complex, it’s time we move that processing over into an application language like C# that has better error handling and testability.”
11 Comments. Leave new
I’ve got one, however it really is an edge case, the app in question executes stored procedures remotely via linked servers.
There’s a few more moving parts, but the important thing for me was figuring out execution scope and catching errors from that execution, the typical TRY/CATCH flow doesn’t pick up remote query issues – it actually thinks everything has worked fine, even if you perform it manually you see an error.
My resolution was to (and I’m copying the note I left myself in the sproc I did 6 months ago) was to refactor my calling sproc to use dynamic SQL to catch outer-scope errors when performing a linked server operation. Reason being, if the linked server fails because the object doesn’t exist, the scope of this error falls outside the EXEC so the TRY/CATCH logic picks it up. Here we can audit the error etc.
It’s the first I came across this type of issue outside the normal usage of TRY/CATCH, and I spent a while understanding what was going on behind the scenes when I came across this particular scenario.
[…] TRY/CATCH Doesn’t Always Work. (Brent Ozar) […]
Not sure I understand this:
Once you add a column to the table should not the dependend stored procedure be marked as invalid?
And when it is invalid is should not be possible to execute.
(Or at least should be validated/compiled again before executing).
So how can we get an exception from within an invalid stored procedure?
I’m not sure what you mean by an invalid stored procedure – stored procedures are never invalidated.
Cached PLANS can be marked invalid when the underlying structure or stats change, but that’s just a plan. When that’s invalidated, it just tells SQL Server to build a new execution plan the next time the query is executed. But it doesn’t make the stored proc invalid – nothing does.
[…] Brent Ozar tries to catch but lets it slip through his fingers: […]
Thank you Brent.
I read your article in horror and I’m not even sure I understand it correctly.
What happens if I’m using SET XACT_ABORT ON; like this: https://stackoverflow.com/questions/69849710/how-to-use-set-xact-abort-on-the-right-way ?
In this post (https://stackoverflow.com/questions/1150032/what-is-the-benefit-of-using-set-xact-abort-on-in-a-stored-procedure) some people ask “Is XACT_ABORT still required if you have the BEGIN TRY-BEGIN CATCH and ROLLBACK with the BEGIN CATCH block in Sql ?”
Some people say “In my opinion SET XACT_ABORT ON was made obsolete by the addition of BEGIN TRY/BEGIN CATCH”.
There is a whole lot of misunderstanding about the topic.
– Does SET XACT_ABORT ON; is based on severity 10/20 or does it works every time?
– How to properly use XACT_ABORT ON; with TRY/CATCH ?
– Was XACT_ABORT ON; replaced by TRY/CATCH ?
I don’t understand and I’m sure I’m not the only one.
Yep! Make sure you read that link I reference in the post.
Thank you Brent.
Long story short, this is how to do it: https://www.sommarskog.se/error_handling/Part1.html#jumpgeneralpattern
I also want to give you a HINT: if you TRY to post on LinkedIn one of your questions with multiple reply you will CATCH how many of us don’t get how to use SET XACT_ABORT ON; .
Personally I cannot tell if I have to put it before the TRY/CATCH or inside the TRY/CATCH or if it’s obsolete to use it with TRY/CATCH
Thanks for the plug for my articles, Brent!
My pleasure, sir! Your articles are always great resources for the community. You rock!