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:

DROP TABLE IF EXISTS dbo.Bookmarks;DROP TABLE IF EXISTS dbo.ProcessLog;CREATE TABLE dbo.Bookmarks( URL VARCHAR(50));GOCREATE 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:

CREATE OR ALTER PROC dbo.AddBookmark @URL VARCHAR(50) ASBEGIN 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 CATCHENDGO 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:

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:

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