SQL Parameter isNull() Boolean/Bit True False Null Problem

[a808y]

I have a store procedure that expects a parameter "@Abnormality bit". Based on the value true, false or null, the query returns all rows with true , false or all values respectively.

I am using the isnull function to check the value, however I get the following 3 faulty condition.

if i use (isnull(@Abnormality,1) = 1 OR Report.abnormalflag = @Abnormality) then 1 and null return all rows

if i use (isnull(@Abnormality,0) = 0 OR Report.abnormalflag = @Abnormality) then 0 and null return all

if i use (isnull(@Abnormality,null) = null OR Report.abnormalflag = @Abnormality) then null return nothing


Please advice.

 

[jmcilhinney]

Just do this:

SELECT * FROM MyTable WHERE @Abnormality IS NULL OR Abnormality = @Abnormality

That covers all bases. If @Abnormality is NULL then all rows match, otherwise only specific rows match.

 

[a808y]

thanks and I appreciate the correct answer. Can you help me understand why did isnull() not work in this case?

 

[a808y]

what is the benefit of isnull() vs is null ?

 

[MattP]

ISNULL (Transact-SQL)

Do not use ISNULL to find NULL values. Use IS NULL instead

The reason it's better is that SQL doesn't have to instantiate all of those instances just to confirm that it is indeed null.