Nulls and the MERGE statement: I need to set a value to infinity. How?

In SQL Server 2008, I'm using MERGE. Everything is fine except that I have 2 nullable columns. If I pass a null value and the target isn't null, MERGE doesn't see a difference (evals against null = false per BOL). If I use IsNull on both sides (source & target) that works, but has the issue of potentially mis-evaluating a value.

What I mean by the last statement is, if I say:

WHEN MATCHED AND NOT (IsNull(tgt.C, 0) = IsNull(src.C, 0)) THEN

then if tgt.C is null and src.C = 0, no update will be performed. No matter what substitute value I choose, I'll have this problem.

I also tried the "AND NOT (...true...)" syntax since BOL states that evaluations against null result in FALSE. However, it seems they actually result in NULL and do not result in my multi-part statement becoming false.

I thought one solution is to use NaN or -INF or +INF since these are not valid in target. But I can't find a way to express this in the SQL.

Any ideas how to solve this?

EDIT:

The following logic solves the problem, but it's verbose and won't make for fast evals:

declare @i int, @j int

set @j = 0
set @i = 0

if ISNULL(@i, 0) != ISNULL(@j, 0) OR 
    ((@i is null or @j is null) and not (@i is null and @j is null))
    print 'update';

14
задан IamIC 22 December 2010 в 13:58
поделиться