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!
