I was working with a customer that was doing some error handling in procs and helped them do some error tracking. As we were working through things, I realized that some of functions working with errors operated differently than I expected.
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
The Error Functions
There are a number of error functions available to you in modern SQL Server. We have:
All of these functions have the same clause in their docs, which says, “ xxx returns NULL when called outside of the scope of a CATCH block.”
That was something I didn’t realize. I’d assumed I could run this:
SELECT 1/0
SELECT ERROR_SEVERITY(), ERROR_MESSAGE(), ERROR_STATE()
However, if I run this, I get the error, but my results are NULL, NULL, NULL.
If I want the values, I need to do this:
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
SELECT ERROR_SEVERITY(), ERROR_MESSAGE(), ERROR_STATE()
END CATCH;
This will return my 16, Divide by zero error encountered., 1
In general, you ought to be using TRY..CATCH blocks for error handling. We do want to ensure that we are doing our best to deal with problems in code and not just expect all errors will be managed by the application. As much as possible, we should try to gracefully fail and give the application or client something useful.
Along with TRY..CATCH, learn to use THROW, and ensure you’re adding some error handling to older code. This is an easy refactoring add to existing code, and it’s simple to enhance future code to make it more maintainable.
SQL New Blogger
This is a quick look at the functions that capture error information, and noting a limitation I didn’t realize. It’s short, simple, and took me about 10 minutes.
This is one of those topics that dev managers, especially front end based ones, appreciate. Doing a post on this topic on your blog might get someone to ask you about error handling, and with a little practice (and a few posts), you’ll be able to talk about this topic confidently.
Voice of the DBA 
Ever since I learned about the Try/Catch command (15 years ago maybe?) I NEVER make any Changes to data without uisng it unless that change is guranteed to be an update only to a single row in 1 table. Otherwise it goes within thhe TRY section of a TRY/CATCH command. This way no partial update/insert/delete is ever made. I go round and round with our software vendor about this because they have no proelms with running a string of DML’s that are effectively interdependent without placing thenm within something like a TRY/CATCH.
LikeLike
I think that’s great, and it’s rare. I almost never see try..catch in code, which is sad.
LikeLike
Pingback: T-SQL Error Functions – Curated SQL