SQL Interview 23: Logical issues related to ISNULL in SQL Server T-SQL

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Medium

Question:

A query included within a stored procedure, has a predicate written like this:

WHERE ISNULL(ProductSize, '') = ISNULL(@ProductSize, '')

A problem has been identified with this predicate. The system needs to be able to differentiate between NULL values for product size and blank strings. Currently both are treated as though they are the same.

What is the issue and how could you rewrite it to avoid the issue?

Answer:

The issue is that whatever value is passed as the second parameter to ISNULL in this case, won’t be treated differently to the lack of a value (i.e., NULL). The blank string is an example of a magic value i.e., one that means something different to what it says.

There are many ways but the following would work:

WHERE ProductSize = @ProductSize
OR (ProductSize IS NULL AND @ProductSize IS NULL);

2025-02-23