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'
3条答案
按热度按时间sqxo8psd1#
I would prefer
It is a little clearer in my opinion
djp7away2#
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...
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.l7mqbcuq3#
I would rather choose this