SQL Querying Null Parameters

I was having problems with querying null parameters, mainly because of the syntax.  For instance, I have a query:

select * from MyTable where NullParameter = @Param and OtherParameter = @OtherParam

But I didn't get any results.  When I attempted this:

select * from MyTable where isnull(NullParameter, '') = isnull(@Param, '') and OtherParameter = @OtherParam

it worked.  Essentially, I brought the parameters to the same level when they were null.  I suspect it's because you have to use the is keyword whenever you reference null.  So, doing this works.  Any more knowledgable SQL developers want to provide a better solution, please do so in the comments.

Published Wednesday, December 20, 2006 12:40 PM by bmains
Filed under:

Comments

# re: SQL Querying Null Parameters

Friday, December 22, 2006 8:16 PM by IDisposable

Once again I want to discourage you from "doing math" on columns in the WHERE clause. It prevents any indexes from being used to speed the query.

For you exact case, if you don't know if the input parameter is NULL or not, then the simplest query would be:

SELECT *

FROM myTable

WHERE

      ((NullParameter = @Param)

      OR (NullParameter IS NULL AND @Param IS NULL))

AND (OtherParameter = @OtherParam)

Of course the ugliness of this query clearly calls out the evil nature of NULL and why I always use the NULL Object Pattern instead (where you figure out what a "null" value means and create an entry that takes the place of NULL.  For example, don't have a NULL DateOfDeath, use 12/31/9999 23:59:59.997 instead... that way someone isn't dead if GetDate() is less than DateOfDeath... the same applies for foreign keys, create a 'not selected' row that your code can clearly identify and handle... this centralizes the "what about null" logic in one place... instead of making you echo it over and over in every SQL query, view and stored procedure

The leading UI suite for ASP.NET - Telerik radControls
Outstanding performance. Full ASP.NET AJAX support. Nearly codeless development.