19

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.

Jonathan Allen
  • 68,373
  • 70
  • 259
  • 447
  • 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)`. – Andriy M May 15 '11 at 07:34
  • possible duplicate of [T-SQL XOR Operator](http://stackoverflow.com/questions/5411619/t-sql-xor-operator) – BuZZ-dEE Jun 06 '14 at 09:34

3 Answers3

20

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
Chris Fulstow
  • 41,170
  • 10
  • 86
  • 110
  • 1
    The first half of the expression is essentially `((@UserName IS NULL) OR (@EditorKey IS NULL))`, according to [De Morgan's laws](http://en.wikipedia.org/wiki/De_Morgan%27s_laws). – Andriy M May 15 '11 at 07:41
  • 1
    @Andriy Agreed, I'm treating `(@UserName IS NOT NULL)` as a general case boolean expression without simplifying any of the logic. – Chris Fulstow May 15 '11 at 07:45
  • 2
    It isn't pretty, but at least it will scale. – Jonathan Allen May 16 '11 at 06:13
  • 1
    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. – Chris Fulstow May 16 '11 at 07:53
1

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.

quillbreaker
  • 6,119
  • 3
  • 29
  • 47
0

There's a bitwise XOR, but it's not necessarily what you want:

http://msdn.microsoft.com/en-us/library/ms190277.aspx

In your particular case, I find it more immediate to rewrite it like so:

IF (@UserName IS NULL) = (@EditorKey IS NULL) BEGIN
Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154
  • Love it. To morph this into true XOR, `((logical condition) <> (other logical condition))`. – Ben Mosher Apr 18 '12 at 15:39
  • 1
    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 '='" – shannon May 08 '13 at 12:42
  • @shannon: Shouldn't you be wrapping that print in between begin/end? – Denis de Bernardy May 08 '13 at 12:47
  • @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. – shannon May 08 '13 at 12:50
  • 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. – Denis de Bernardy May 08 '13 at 14:13