optional search parameters in sql query and rows with null values
- by glenn.danthi
Ok here is my problem :
Before i start the description, let me to tell you that I have googled up a lot and I am posting this question for a good optimal solution :)
i am building a rest service on WCF to get userProfiles... the user can filter userProfiles by giving something like userProfiles?location=London
now i have the following method
GetUserProfiles(string firstname, string lastname, string age, string location)
the sql query string i built is:
select firstname, lastname, .... from profiles where (firstName like '%{firstname}%') AND (lastName like '%{lastName}%') ....and so on with all variables being replaced by string formatter.
Problem with this is that it filters any row having firstname, lastname, age or location having a null value....
doing something like (firstName like '%{firstName}%' OR firstName IS NULL) would be tedious and the statement would become unmaintanable! (in this example there are only 4 arguments, but in my actual method there are 10)
What would be the best solution for this?....How is this situation usually handled?
Database used : MySql
