Is there an exclusive OR operator in T-SQL?

问题:

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.

回答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

回答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.

回答3:

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