SQL's NULL Marker and Its Propagation

NULL is a SQL's marker to indicate missing information or unknown status. It is essential part of relational database and SQL. One of NULL feature is that it can propagate the result in many cases and they do make sense.

For example, 1 + NULL is NULL, or NULL in any mathematical expression will result NULL. That's OK. However, it further propagates to SQL internal functions, like LEN and CHARINDEX. It is very difficult to argue why not the result is NULL. I have to say it will depend on your case.

Recently, I have been working on a project with Microsoft SQL 2005. I did not realize that LEN and CHARINDEX functions return NULL if its parameters are NULL. That caused problem in my stored procedures. What I need these functions is to find out if a specified string exists in an expression. The expression is a value retrieved from db table. For my case, if the value is NULL, the result should be either LEN = 0 or CHARINDEX = 0.

As a result, I have to overwrite these internal functions as user defined functions such as udf_LEN() and udf_CHARINDEX. Then will handle NULL parameters and return the correct result. For example, I use the following CASE statement to check an input parameter:

DECLARE @v_ret int;
SET @v_ret =  CASE
    WHEN @p_string IS NULL THEN 0
    ELSE LEN(@p_string);

When you use internal functions, you have to aware NULL's implication or propagation in your result.