A coworker recently came to me with a problem. His query returned the set of data he expected … until he added a certain condition. Then, instead of returning a subset of the original result, it returned nothing at all. I sat down to take a look and noticed he was dynamically generating a SQL statement. He demonstrated his process of executing his script to text for review. Then he added his condition to one of his subqueries, and the output went blank.
Fortunately for both of us, I’ve dealt with this sort of problem many times in my own code as well as others’. The trouble was his condition made the subquery sometimes return null, and all results (including the null) were being concatenated into a single SQL string. In case you don’t already know this, nulls are world-class cleaners; the kind your parents wanted you to be. Concatenating a single null to a string cleans up everything very neatly. In fact, everything disappears and is replaced with nothing (i.e., null). To solve his problem, I simply wrapped his subquery in an ISNULL function, and then it worked as expected with or without the filter.
The important lesson here is to always ensure each and every piece of a string cannot possibly be null. Prior to SQL Server 2012, this was typically done using the ISNULL function, but we now have an alternative. Rather than “adding” strings together and wrapping each in ISNULL, we can use the CONCAT function with substrings separated by commas. Let’s take a look at the 2 options:
DECLARE @str1 varchar(10) = 'ABC';
DECLARE @str2 varchar(10);
DECLARE @str3 varchar(10) = 'XYZ';
DECLARE @mystring varchar(20);
SET @mystring =
+ ISNULL(@str2, '')
+ ISNULL(@str3, '')
-- SQL2012 and newer
DECLARE @mystring = CONCAT(@str1, @str2, @str3);
Both methods guarantee a null won’t eliminate your string value, but as you can see, the newer CONCAT function is much simpler to use. If any value is null, the function automatically (implicitly) converts it to an empty string. As an added bonus, it will also convert any non-string values that supports implicit conversion to string.
So remember, contrary to what your parents may have told you about your bedroom, it is possible to be too clean. When that long string you’ve been building suddenly looks a little empty, it may have been swept up by a sneaky little null.