CODEWITHSUNDEEP
sql-servert-sql
GilM
GilM

Reputation: 3761

Comparing Nullable Bits

In Transact-SQL, what's the best way to compare two bit values that can be 0, 1, or NULL?

For example, in stored procedure that UPDATEs a bit column with a parameter value, I'd like to only perform the update if the parameter value is different from the value in the table (the values in the table and the parameter can be 0, 1 or NULL).

I'm considering:

WHERE …
AND (CASE WHEN b=@b OR COALESCE(b,@b) IS NULL THEN 0 ELSE 1 END) = 1

vs.

WHERE …
AND EXISTS (SELECT b EXCEPT SELECT @b)

Is there a good reason to prefer one over the other? Is there a better way to do this?

In this case, only one row will be updated, so I don't think performance is a big issue, unless there's a large difference.

Upvotes: 0

Views: 215

Answers (1)

HABO
HABO

Reputation: 15816

Since the data being compared are nullable bit values you can use Coalesce( B, -1 ) = Coalesce( @B, -1 ) to perform matching of all three values (0, 1 and NULL) by mapping them to 0, 1 and -1.

Coalesce follows the rules for data type precedence. Given a bit (B) and an int (-1) it will convert the bit to an int so that the result is an int value.

The expression is not SARGable, but in this case the OP has stated that the operation is being performed on a single row already identified by PK so the performance of the comparison is not significant.

Note that this is a case of introducing a value (-1) that cannot occur in the original data's data type (bit). It is not the same as picking an arbitrary magic value that probably won't occur, e.g. the year 1800 is much too far in the past to ever turn up in a DateSold column. Except for real estate and various other things.

Bonus tip:

A handy way to confirm the behavior is by using SQL_Variant_Property to display the data type of the result:

declare @B as Bit = 1;
select SQL_Variant_Property( Coalesce( @B, -1 ), 'BaseType' );

Upvotes: 1

Related Questions