How do I search across multiple criteria from a database with SQL?
I am a newbie programmer. I have a page as shown below. I have a database of cars and I have to search from the database from this page. There are many criteria by which users can enter and select values. But the user does not need to fill in or select all the criteria. For example, a user can search for a car that depends only on "Sign" or can perform a search that depends on year = "2007". Fuel = "Diesel" Gear = "Automatic" ... etc. My problem is how to design a query that can determine which controls are relevant or not. I am using C # and MSSQL Server.
source to share
You must make your query flexible enough (handling blank parameters) to work with all search terms. Consider the same example. if the user only entered the label field and was left blank
suppose 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 suggest that you pass all parameters indivitually to SP. this avoids dynamic query building and avoids SQL injection.
source to share
As mentioned earlier, be careful with SQL injection.
I would avoid sp with multiple parameters as those parameters can be quite numerous and sp needs to change. Also, the qill request will be very slow.
From what I have seen, it is better to build the query in code with only the constraints / filters you need and avoid
@param is null OR filed = @param
source to share
here is a very detailed article on how to handle this topic:
Dynamic Search Conditions in T-SQL by Erland Sommarskog
it covers all the problems and techniques of trying to write queries with multiple optional search terms
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 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 Acknowledgments
Revision History source to share
You can compose a query dynamically in your application by concatenating all the fields that have the values specified for them. For example: "... WHERE Model = 'abc' AND Color = 'Blue' AND ...". Fields that have no value will simply not be included in the WHERE clause. If you use this method, you must be VERY careful to check your input fields for SQL injection attacks.
The best way would be to create a stored procedure in the database with parameters for all input fields. You can pass null values for those fields for which the user did not enter a value. However, you must ensure that your SP can handle null parameters correctly and filter your result accordingly.
source to share
you may have something in the lines
select * from cars where model like case when @model = '' then '%' else '%'+@model+'%' end
etc. etc. Ugly, but it should work.
here is a quick query i knocked out 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
Then you add AND clauses ad infinitum using% s when empty and search string when required.
source to share
Linq does not create a select statement until it is actually used. Thus, you can do something similar 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;
}
It was from a cuff, so I might not have 100% syntax, but it should be close.
I should have mentioned this is the method that your form would call to get the result set.
source to share