CODEWITHSUNDEEP
t-sql
Dustin Davis
Dustin Davis

Reputation: 14585

SQL nullable bit in where clause

create procedure [dbo].[MySproc]
@Value bit = null
as

select columna from tablea where columnb = @Value

This does not work if I pass in null to the parameter. Obviously when I change the predicate to columnb is null it works.

What can I do to get this to work without using conditional logic (if/else)?

Update: I figured it out based on @gbn answer

where (@Value is null and columnb is null) or (columnb = @Value)

Upvotes: 0

Views: 1125

Answers (1)

gbn
gbn

Reputation: 432210

Assuming you want true if 1=1, 0=0 or NULL=NULL

select columna from tablea 
where columnb = @Value OR (columnb IS NULL AND @Value IS NULL)

What about 0/1 in column, NULL as parameter. Normally, this would be "just give me the rows"

where columnb = ISNULL(@Value, columnb)

Your logic doesn't make sense because you are using NULL to mean something...

Upvotes: 4

Related Questions