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

I am a newbie programmer. I have a page looks like below. I have a car database and i have to make a search from database with this page. There are many criterias that Users can enter and select values. But User doesn’t have to fill or select all criterias. For example User can search a car that only depends on “Mark” or can search that depends on Year=”2007″ Fuel=”diesel” Gear=”Automatic”… etc. My problem is how to design a query that can detect which controls have value or not. I use C# and MSSQL Server.

alt text http://img8.imageshack.us/img8/5781/searchad.jpg

  • concatenate two fields in a dropdown
  • SQL Formatting Tool
  • Connecting to MSSQL server via php-pdo?
  • Inner Joining three tables
  • Does the order of columns in a WHERE clause matter?
  • Retrieving only PRINT command from SQL Server procedure in VB.NET
  • Why does it give an error when casting a string to GUID in SQL Server?
  • How do I split a sentence in SQL Server 2005
  • Query with multiple line results in only one line
  • SQL Query to separate address into multiple columns
  • How to test SqlServer connection without opening a database
  • Boolean 'NOT' in T-SQL not working on 'bit' datatype?
  • 6 Solutions collect form web for “How to search with multiple criteria from a database with SQL?”

    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.

    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

    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

    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.

    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.

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.