How to search with multiple criteria from a database with SQL?

Answer 1

You can compose a query dynamically in your application by concatenating all of the fields that have values specified for them. For example "... WHERE Model = 'abc' AND Color = 'Blue' AND ...". Fields that don't have values just won't be included in the WHERE clause. If you use this method you need to be VERY careful that you check your input fields for SQL injection attacks.

A better way would be to create a stored procedure in the database that has parameters for all of the input fields. You can pass in null values for those fields that the user hasn't input a value for. However, you have to make sure your SP can properly handle parameters with null values and filter your result set accordingly.

Answer 2

You should construct your query flexible enough (handling unfilled parameters) to work an all search conditions. Consider the same example. if the user entered only the mark field, and remaining left blank

sample code, assume you have these parameters

@mark varchar(20)
@series varchar(20)
@model varchar(20)

select * from tbl 
where (@mark is null or markColumn=@mark) and
(@series is null or series Column=@series ) and
(@model is null or model Column=@model )

I would recomment you to pass all parameters indivitually to SP. this avoids the dynamic construction of query and will avoid SQL injections.

Cheers

Ramesh Vel

Answer 3

As mentioned before, be carefull of sql injection.

I would avoid the sp with multiple params, as these params can be quite a number, and the sp would need to change. Also, the query qill be very slow.

From what i have seen, it is better to build the query in the code, with only the required limitters/filters and avoid the

@param is null OR filed = @param

Answer 4

you can have something along the lines of

select * from cars where model like case when @model = '' then '%' else '%'+@model+'%' end

and so on and so forth. Ugly, but it should work.

here's a quick query I knocked up as an example

declare @model varchar(20)
set  @model = 't'
select  * 
from    (
      select 'one' as model
      union
      select 'two'
      union
      select 'three' 
     ) as model
where   model like case when @model = '' then '%' else '%'+@model+'%' end

You then add AND clauses ad infinitum, using %s when empty and the search string when required.

Answer 5

Linq does not create the select statement, until it's actually used. Thus, you can do something akin to the following:

public IQueryable<object> GetObjects(int a, string b)
{
     var q = from i in items
             where(i=>i.id == a)
             select i;
     if(!string.IsNullOrEmpty(b))
         q = q.where(o=>o.Name == b);
     return q;
}

That was off the cuff, so I might have the syntax not 100%, but it should be close.

I should have mentioned, this is the method which your form would call to get the result set.

Answer 6

here is a very comprehensive article on how to handle this topic:

Dynamic Search Conditions in T-SQL by Erland Sommarskog

it covers all the issues and methods of trying to write queries with multiple optional search conditions

here is the table of contents:

  Introduction
      The Case Study: Searching Orders
      The Northgale Database
   Dynamic SQL
      Introduction
      Using sp_executesql
      Using the CLR
      Using EXEC()
      When Caching Is Not Really What You Want
   Static SQL
      Introduction
      x = @x OR @x IS NULL
      Using IF statements
      Umachandar's Bag of Tricks
      Using Temp Tables
      x = @x AND @x IS NOT NULL
      Handling Complex Conditions
   Hybrid Solutions – Using both Static and Dynamic SQL
      Using Views
      Using Inline Table Functions
   Conclusion
   Feedback and Acknowledgements
   Revision History