August 12, 2011 at 12:39 pm
Hi,
I'm trying to fetch records filtered with a field that may have NULL, 0, or 1 for a value. I want to exclusively get the one's with 1's or get them all based on a parameter value.
Orginally I went for a parameter that held (1,1) or (0,1) and plugged that in:
SELECT * FROM mytable WHERE someflag IN (@Parameter)
This filtered only 1's correctly, but missed the NULLS when I wanted to get them all. And of course WHERE IN (NULL,0,1) returns nothing.
So my question is: How to write this in one statement where I can evaluate a parameter (data type doesn't matter) to get only 1's or get all (NULL's, 0's, and 1's) based on the value of the parameter?
August 12, 2011 at 12:44 pm
I think I figured it out.
WHERE (@Parameter IS NULL OR (@Parameter IS NOT NULL AND someflag = 1))
August 12, 2011 at 1:44 pm
zach.hassler (8/12/2011)
I think I figured it out.WHERE (@Parameter IS NULL OR (@Parameter IS NOT NULL AND someflag = 1))
Often times, when dealing with null values in a column of data I'd convert the null in my code to a -1 (assuming it is a valid within the scope of data).
where isnull(<column name>,-1) in (-1, 0, 1)
Something I don't do often but is handy.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
August 12, 2011 at 2:02 pm
Yes, that's a good idea to convert null to something useful for a comparison. That would work also. I circumvented the comparison problem by using my @Parameter as a flag, but your code looks cleaner -- no nested conditionals. Thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply