SQLTeam.com | Weblogs | Forums

Where case is not null


#1

Trying to add a CASE statement to a WHERE clause.
If the type = 1 then look for field values that are null.
If the type = 2 then look for field values that are not null.

This is not working and have been at it for a while...

    WHERE ConvertedDate IS 
    CASE WHEN @ConvertedDateType = 1 THEN NULL 
    WHEN @ConvertedDateType = 2 THEN NOT NULL
    END

This is part of a very large procedure and is the last part.
And it's too large to consider creating a string and using EXEC.
Thanks for any input.


#2
WHERE
    (
           (@ConvertedDateType = 1 AND ConvertedDate IS NULL)
        OR (@ConvertedDateType = 2 AND ConvertedDate IS NOT NULL)
    )

#3

Perfect. Thank You.
Don't know why I didn't think of that.