Simulating XOR in T-SQL

I can’t believe it.  My first post in months and it is about… SQL.  Arghhh, will I never get out of the DB hell?

So, what is this one about?  I was looking for a way to perform an XOR operating in T-SQL.  I needed to test if certain optional parameters for a stored procedure had been supplied, with the restriction that only one of the parameters could be present, but not both or neither. Essentially, I needed a way to avoid writing

IF (@A IS NULL AND @B IS NOT NULL) OR (@B IS NULL AND @A IS NOT NULL)

Nasty, eh?

The solution is to use the COALESCE function which returns the first non-NULL value in a list, or NULL if all values in the list are NULL.
The above can be rewritten as

IF (COALESCE(@A, @B) = COALESCE(@B, @A))

This also takes advantage of the fact that SQL implements a warped version of ternary logic to deal with with NULLs: IF @A and @B are both NULL then IF (@A = @B) returns false (actually, it returns UNKNOWN, which then maps to false) (http://en.wikipedia.org/wiki/Trinary_logic#Application_in_SQL). Note: this means that the reciprocal of the XOR statement above

 IF (COALESCE(@A, @B) <> COALESCE(@B, @A))

does not serve as a NOT XOR.

The following code shows the COALESCE being used to duplicate XOR (where NULL => 0 and any value => 1):

DECLARE @ca VARCHAR(10);
DECLARE @cb VARCHAR(10);

SET @ca = NULL; SET @cb  = NULL;
SELECT @ca, @cb, CASE WHEN COALESCE(@ca, @cb) = COALESCE(@cb, @ca) THEN 'True' ELSE 'False' END;

SET @ca = NULL; SET @cb  = 'def';
SELECT @ca, @cb, CASE WHEN COALESCE(@ca, @cb) = COALESCE(@cb, @ca) THEN 'True' ELSE 'False' END;

SET @ca = 'abc'; SET @cb  = NULL;
SELECT @ca, @cb, CASE WHEN COALESCE(@ca, @cb) = COALESCE(@cb, @ca) THEN 'True' ELSE 'False' END;

SET @ca = 'abc'; SET @cb  = 'def';
SELECT @ca, @cb, CASE WHEN COALESCE(@ca, @cb) = COALESCE(@cb, @ca) THEN 'True' ELSE 'False' END;

DECLARE @ia int;
DECLARE @ib int;

SET @ia = NULL; SET @ib  = NULL;
SELECT @ia, @ib, CASE WHEN COALESCE(@ia, @ib) = COALESCE(@ib, @ia) THEN 'True' ELSE 'False' END;

SET @ia = NULL; SET @ib  = 5;
SELECT @ia, @ib, CASE WHEN COALESCE(@ia, @ib) = COALESCE(@ib, @ia) THEN 'True' ELSE 'False' END;

SET @ia = 4;    SET @ib  = NULL;
SELECT @ia, @ib, CASE WHEN COALESCE(@ia, @ib) = COALESCE(@ib, @ia) THEN 'True' ELSE 'False' END;

SET @ia = 4;    SET @ib  = 5;
SELECT @ia, @ib, CASE WHEN COALESCE(@ia, @ib) = COALESCE(@ib, @ia) THEN 'True' ELSE 'False' END;