Sometimes it's what you don't know that hurts. Sometimes it hurts a lot!

Recently one of our developers was just about to pound his head on the desk in

frustration, trying to figure out why some TSQL was not working correctly. It

looked something like this:

set @SomeVariable = @SomeVariable + @SomeOtherVariable

He was printing out the value of @SomeOtherVariable and was able to see that

it had the correct value, @SomeVariable was correctly null. Yet he was getting a

null!

Do you know why?

In SQL 7 and higher, null + anything = null. Nulls have that weird behavior

of being equal to nothing, not even each other. You can't directly concatenate

anything with a null and wind up with anything except a null. This is commonly

referred to as null propagation (or null propaganda!). Not only is not quite

obvious, its a change from SQL 6.5 where null + variable = variable.

Maybe someone else will take up the cause of arguing for/against this

behavior!

Now that we know the behavior, what's the best way to handle it? Lot's of

nice options:

• Initialize it up front (set @variable = ''). Not the worst habit to

  develop, this is one way to avoid the problem

• Use IsNull (set @SomeVar = IsNull(@SomeVar, '') + Isnull(@SomeVar2, '')
• Use SET CONCAT_NULL_YIELDS_NULL OFF
• Use sp_dboption to set the compatibility level of the database to SQL

  6.5

Once you know it, it's not hard to figure out how to avoid it. Yet I continue

to see code that does plan for the possibility of nulls. Share with your

developers - or better yet, quiz them!