Posted by Plamen Ratchev on 02/06/07 18:11

Hi Paul,

Here is one more option to check. I have seen similar behavior on client
databases when somebody turned the CONCAT_NULL_YIELDS_NULL option to OFF.
Basically when it is OFF a string concatenation like SELECT TAB1.DESCRIPTION
+ NULL will return the string in TAB1.DESCRIPTION, not NULL. When
CONCAT_NULL_YIELDS_NULL is ON then SELECT TAB1.DESCRIPTION + NULL will
return NULL. I am not sure what is the logic and SQL of your application but
if you have string concatenation it could be it.

A good example of how it will affect returned records is if you have a query
like this:

SELECT FirstName + ' ' + MiddleInitial + ' ' + LastName AS EmployeeName
FROM Employees
WHERE FirstName + ' ' + MiddleInitial + ' ' + LastName IS NOT NULL

Then if you have MiddleInitial set to NULL for some records you will see
different results based on CONCAT_NULL_YIELDS_NULL being ON or OFF.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Back to original message]