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

a808y

Member
Joined
May 21, 2009
Messages
9
Programming Experience
Beginner
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

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,553
Location
Sydney, Australia
Programming Experience
10+
Just do this:
VB.NET:
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

Member
Joined
May 21, 2009
Messages
9
Programming Experience
Beginner
thanks and I appreciate the correct answer. Can you help me understand why did isnull() not work in this case?
 
Top Bottom