Dreaded NULL Values and IN, Conditional Where

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dreaded NULL Values and IN, Conditional Where

Author

Topic

sassinak18
Starting Member

1 Post

Posted - 2010-07-26 : 20:55:53

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

Kristen
Test

22859 Posts

Posted - 2010-07-27 : 01:39:31

Commonly this is handled by


WHERE (@StateIDs IS NULL OR State.StateID = @StateIDs)

thus the user can provide a value for @StateIDs, in which case it will be matched, or the user can leave it "NULL" in which case all rows are matched.

If I asked for @StateIDs=XX then I would expect State XX to be included, of course, but I'm not sure I would want rows with a NULL state included?

You could have another criteria for "Include rows with NULL StateID" so it would then be


WHERE (@StateIDs IS NULL OR State.StateID = @StateIDs OR (@IncludeNullState = 1 AND State.StateID IS NULL))

I also wonder if a JOIN would be more efficient than an IN - not really sure how your udf_SplitAndCoalesce() is working to provide an IN list, but when I do this type of split I use something like


SELECT Col1, Col2, ...
FROM dbo.MyTable AS T
    LEFT OUTER JOIN dbo.udf_MySplit(@StateIDs, ',')
        ON MySplitValue = T.StateID
WHERE (@StateIDs IS NULL OR MySplitValue = T.StateID)

Sachin.Nand

2937 Posts

Posted - 2010-07-27 : 02:34:10

Try this


select * from yourtable where stateid in (case 
when udf_SplitAndCoalesce(@StateIDs, State.StateID, ',')
 is not null then udf_SplitAndCoalesce(@StateIDs, State.StateID, ',') else stateid end)

The problem with above code would be that udf_SplitAndCoalesce(@StateIDs, State.StateID, ',')
will be called twice in case.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources