Click here to Skip to main content
14,175,662 members
Rate this:
Please Sign up or sign in to vote.
I am using MS Sql Server 2008. My query has the code

WHERE RepCode = @RepCode

When @RepCode is set to a non-null value, the query behaves as I would expect. When I set the parameter to DBNull.Value, the query returns nothing, even though there is data that should be returned. If I rewrite it

WHERE RepCode IS @RepCode

then the query works correctly for a null value but not for a non-null value.

I assume that there is some way to write a parameterized query where the parameter may or may not be null that will always return the correct value. How would I do this?
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

You have pretty much discovered the reason for the problem. NULL is never equal to anything, not even to another NULL.

One workaround for this is that you eliminate NULL's from the comparison by replacing it with another value. Perhaps the easiest way is to use COALESCE[^]. Using that, your example would be something like:
WHERE COALESCE(RepCode, -1) = COALESCE(@RepCode, -1)

So the idea is that you replace the NULL with some other value.

Important: Just make sure that the value does not exist in the data since that would bring false results.
Gregory Gadow 7-Sep-11 16:26pm
D'oh! Yup, that works: COALESCE(RepCode, '') = COALESCE(@RepCode, ''). One of those solutions that is too obvious. Thanks.
Wendelius 7-Sep-11 16:40pm
You're welcome :)
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

It doesn't help you specifically, but MySQL has the <=> operator, called the "Null-safe equal-to" operator, or the "spaceship" operator.

I don't think T-SQL has this :(

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Cookies | Terms of Service
Web04 | 2.8.190525.1 | Last Updated 7 Sep 2011
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100