SQL Server Null Logical XOR

Question

I'm creating a constraint on a (Microsoft) Sql Server 2008 table. I have two columns that are different datatypes. One column must be null at all times, but not both (A logical exclusive OR/XOR). I have a working expression currently.

(@a is null or @b is null) and not (@a is null and @b is null)

My question is Is there a cleaner or shorter way to write this code?

To test it you can use this code...

declare @a int
declare @b varchar(5)

set @a=1
set @b='XXXXX'

if (@a is null or @b is null) and not (@a is null and @b is null)
  select 'pass'
else
  select 'fail'

Answer 1

I would prefer

if (@a is null and @b is not null) or (@a is not null and @b is null)

It is a little clearer in my opinion

Answer 2

OK, here is a (albeit stupid) suggestion if you are working with all strings. And it scales to multiples greater than 2 where you want a particular number of non-nulls. Again, its stupid and not really shorter but it was too fun to pass up mentioning...

where LEN(ISNULL(right(@a+'x',1),'')+ISNULL(right(@b+'x',1),'')) = 1

To make it work with your stuff, You'd have to CAST the [int], I suppose. Doing string "surgery" in the where clause before matching is evil and liable to be real slow. But if you had 6 columns and needed exactly 1 to be not-null this trick would scale up.

Basically, we're using LEN to count the not-nulls. The +'x' ensures that you don't count a '' as null. You could leave that off when testing [char] columns.