This week, while searching for a bug, I came across a common issue.  Many layers down in the code, a stored procedure was called.  The SP concatenated 2 columns, which allowed null values.  The majority of the time, empty string was given as default.  Some new code, placed null in one but not both fields.

Because Null is not a data value, but a marker for unknown value, using string concatenation operators on Null results in an unknown value, which is represented by Null.  In the following example, concatenating the string values shows how this can cause issues:

DECLARE @str1 VARCHAR(100)
DECLARE @str2 VARCHAR(100)
DECLARE @str3 VARCHAR(100)

SET @str1 = 'Hello'
SET @str2 = NULL
SET @str3 = 'World'

-- Nulls concatenated with anything is Null
SELECT @str1 + @str2 + @str3

-- Use ISNULL to change Nulls to empty strings before concatenation
SELECT Isnull(@str1, '') + Isnull(@str2, '') + Isnull(@str3, '') 

The 1st Select results in a return value of: NULL

The 2nd Select results what we expected: HelloWorld

If your database structure allows nulls and you use the columns in a concatenation (+), even if your code defaults to empty string, avoid future mishaps by including the ISNULL function.