Is there an exclusive OR operator in T-SQL?

Question

This is my statement

IF (@UserName IS NULL AND @EditorKey IS NULL) OR (@UserName IS NOT NULL AND @EditorKey IS NOT NULL) BEGIN
    RAISERROR ('One of @UserName, @EditorKey must be non-null.', 15, 0)
    RETURN
END

What I want is to be able to do something like this:

IF (@UserName IS NOT NULL) XOR (@EditorKey IS NOT NULL) BEGIN
    RAISERROR ('One of @UserName, @EditorKey must be non-null.', 15, 0)
    RETURN
END

For two parameters it isn't that big of a deal, but some procs have three or four where in only one may be passed and the rest should be null.

Solution 1

Not very succinct, but you could expand out the logic like this:

WHERE
    (NOT ((@UserName IS NOT NULL) AND (@EditorKey IS NOT NULL))) AND
    ((@UserName IS NOT NULL) OR (@EditorKey IS NOT NULL))

Or use the bitwise XOR operator (^):

WHERE
    (CASE WHEN (@UserName IS NOT NULL) THEN 1 ELSE 0 END) ^
    (CASE WHEN (@EditorKey IS NOT NULL) THEN 1 ELSE 0 END) = 1

You can use a similar approach where there are three or four parameters, and exactly one must have a value:

WHERE
    (CASE WHEN (@Var1 IS NOT NULL) THEN 1 ELSE 0 END) +
    (CASE WHEN (@Var2 IS NOT NULL) THEN 1 ELSE 0 END) +
    (CASE WHEN (@Var3 IS NOT NULL) THEN 1 ELSE 0 END) +
    (CASE WHEN (@Var4 IS NOT NULL) THEN 1 ELSE 0 END) = 1

Solution 2

As a cheat, you can do:

If @UserName+@EditorKey is null and coalesce(@UserName,@EditorKey) is not null

It's shorter, but that may be the only thing it has going for it.

Comments

[Andriy M]

Your first statement, (a AND b) OR (NOT a AND NOT b), is a replacement for a EQU b (logical equivalence), which is logically the negation of XOR. The latter is represented, for example, like this: (a AND NOT b) OR (NOT a AND b) or like this: (a OR b) AND (NOT a OR NOT b).

[BuZZ-dEE]

possible duplicate of T-SQL XOR Operator

[Andriy M]

The first half of the expression is essentially ((@UserName IS NULL) OR (@EditorKey IS NULL)), according to De Morgan's laws.

[Chris Fulstow]

@Andriy Agreed, I'm treating (@UserName IS NOT NULL) as a general case boolean expression without simplifying any of the logic.

[Jonathan Allen]

It isn't pretty, but at least it will scale.

[Chris Fulstow]

It's slightly unsatisfying if there isn't a better way to do it. The main limitation seems to be the lack of a proper bool data type in TSQL.

[Ben Mosher]

Love it. To morph this into true XOR, ((logical condition) <> (other logical condition)).

[shannon]

Correct me if I'm wrong, but you the SQL server Boolean data type is not comparable? "IF (NULL IS NULL) = (NULL IS NULL) PRINT 'COMPARABLE'" yields "Incorrect syntax near '='"

[Denis de Bernardy]

@shannon: Shouldn't you be wrapping that print in between begin/end?

[shannon]

@Denis: It's surely better form but not necessary for single operations. The result is the same. Can you give it a try and tell me if I'm wrong? I'm writing similar constraints at this very moment. Would love to use this technique and upvote you.

[Denis de Bernardy]

Tbh, I merely translated what I do Postgres, where it works just fine... I occasionally use the construct in check constraints to make fields mutually exclusive.