Tag: T-SQL

I was working with a customer and discussing how to do error handling. This is a short post that looks at how you can start adding TRY.. CATCH blocks to your code.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

TRY CATCH

This is a common error handling technique in other languages. C# uses it, as does Java, while Python has TRY EXCEPT. There are other examples, but these are good habits to get into when you don’t know how code will behave or if there is something in your data or environment that could cause an issue.

In SQL, I think many of us get used to writing one statement in a query and forget to do error handling, or transactions. However, this can be a good habit as your code might grow and people might add more statements that should execute.

A classic example of code is someone writing this:

DECLARE
   @id INT = 2
, @name VARCHAR(20) = 'Voice od the DBA'
, @stat INT = 1;

BEGIN TRAN;
INSERT dbo.Customer
   (CustomerID, CustomerName, status)
VALUES
   (@id, @name, @stat);
IF @@ERROR = 0
   COMMIT;
ELSE
   ROLLBACK;

Note that this does look for an error and then decide what to do. However, we could be better, especially if we wanted to possibly add a second insert or other work. We could do this:

DECLARE
   @id INT = 2
, @name VARCHAR(20) = 'Voice od the DBA'
, @stat INT = 1;

BEGIN TRY
     BEGIN TRAN;
     INSERT dbo.Customer
     (CustomerID, CustomerName, status)
     VALUES
     (@id, @name, @stat);
     COMMIT
END TRY
BEGIN CATCH
     ROLLBACK 
END CATCH

It doesn’t look like much, but this code could easily be enhanced with a better pattern. We can capture the various error messages like this:

DECLARE
   @id INT = 2
, @name VARCHAR(20) = 'Voice od the DBA'
, @stat INT = 1;

BEGIN TRY
     BEGIN TRAN;
     INSERT dbo.Customer
     (CustomerID, CustomerName, status)
     VALUES
     (@id, @name, @stat);
     COMMIT
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );

    WHILE @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION;
    END 
END CATCH

In this case, we have a few statements that work with the error, in this case using RAISERROR to raise this. We could also use THROW or add something else. If we had more inserts, like to a child table, we could encapsulate them all here. What’s more, if we had logging, we could log this before the rollback to another system if our logging were not transaction dependent.

Using TRY CATCH is really just structuring your code differently. Ideally, using something a snippet in SQL Prompt so your developers have an easy way to standardize error handling.

SQL New Blogger

This post took me about 15 minutes to structure and test. I looked at a few patterns, and I liked the one in this Stack Overflow answer as a good way to generically implement this structure.

You could write a similar post showing your next boss how you implement error handling, transactions, anything. Give it a try.

For years I’ve assumed I knew the string defaults, but I realized that’s not right. This post looks at what I learned.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Declaring VARCHAR variables

I learned a couple things. First, this is invalid code:

The parenthesis aren’t needed, and cause an error. But if I declare just the word, I can add a string. The string in this code is more than 30 characters, which I’ve always assumed is the default length.

DECLARE @s VARCHAR;

SELECT @s = 'this is a test of a fairly long string'

SELECT @s

When I run this, however, I only get one character back.

Why is that? Well, the default length is on, according to the docs.

When is it 20? When we use CAST/CONVERT. In that case, it’s 30. Code from the docs shows this:

I’ve known this happens with CAST, but I didn’t realize the default length was 1. That’s interesting, and hopefully something no one lets slip into production when it would cause a problem.

A good lesson is to always declare your length, and don’t make that MAX if you don’t need it.

SQL New Blogger

This post took me about 10 minutes to write, once I realized the issue. I spent a few minutes grabbing links, as I’d had some of the code written once I was testing what I’d read.

You could do the same thing. Show some learning, show some code, show how you change things.

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:

• ERROR_LINE()
• ERROR_MESSAGE()
• ERROR_NUMBER()
• ERROR_PROCEDURE()
• ERROR_SEVERITY()
• ERROR_STATE()

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.