SQL: Dynamic Where Clause using IN statement dying on records with NULL values

Question

I am trying to implement search funtionality for our database through a website. The database has one master table and five tables that are foreign keyed to the master (detail tables). Detail tables hold nullable integer values that are hooked to lookup tables in the application. I want to allow a user to select any/all values from a lookup table and pass it to a stored procedure as a comma separated string. The problem I have run into has to do with when the record has NULL values because it returns UNKNOWN so I do not get any rows back.

here is an example of what I want in the where clause:

PSEUDOCODE

Input Variable
  @StateIDs VARCHAR(MAX)

  @StateIDs = '1, 2, 3' //I have a split function to split this up
    //@StateIDs could have zero or all 50 states in it so this needs to work with either

  WHERE
    State.StateID IN (udf_SplitAndCoalesce(@StateIDs, State.StateID, ','))

    //udf_SplitAndCoalesce is used to split up the csv input parameter and if that is null to return the current value in the record in the table

This works unless @StateIDs = NULL the where clause becomes

    WHERE State.StateID IN (State.StateID)

which is fine unless State.StateID = NULL then it becomes

WHERE State.StateID IN (NULL)

which will return UNKNOWN (no rows)

If the csv list in @StateIDs is null then I really dont want that part of the Where clause to be there but I dont know of a way of excluding or including entire statements conditionally. The biggest problem is there are about twenty differnt inputs that are possible so I cannot just write two different select statements one when @StateIDs is not null and one for when it is.

I am ultimately trying to create a dynamic where clause without executing the statement as a string using sp_executesql.

I have been working on this for a couple of days now and created a bunch of different solutions but each one is never quite what I need and it comes down to dealing with NULL.

Any help people can give me would be much appreciated.

Mike

Answer 1

You'll have to update each WHERE clause, but can you do something like this (note the WHERE clause is surrounded by parenthesis)?

WHERE
    (State.StateID IN (udf_SplitAndCoalesce(@StateIDs, State.StateID, ',')) 
        OR @StateIDs IS NULL)

If @StateIDs is NULL, the OR @StateIDs IS NULL clause will effectively return all rows as if the IN statement wasn't there, since the whole statement will evaluate to TRUE.