CODEWITHSUNDEEP
sqlsql-servert-sql
Paritosh
Paritosh

Reputation: 4503

suggestions on how to build query for search with multiple options

We are building a search form for users to search our database, the form will contain mulitlpe fields which are all optional. Fields including:

  1. company name
  2. company code
  3. business type (service or product)
  4. Product or Service
  5. Product or Service subtype --> this will depend on what is chosen in #4

Basically the users can fill all or just some of the fields and submit the form. How best should we handle the sql for this? Is it best to use dynamic sql, build out the where clause in our webpage and then forward that to the sql stored procedure to use as it's where clause? Or is it better to pass all the values to the stored procedure and let it build the where clause dynamically. Also is dynamic sql the only way? I wasn't sure if using EXECUTE(@SQLStatement) is a good practice.

Upvotes: 0

Views: 680

Answers (2)

Kevin Kunderman
Kevin Kunderman

Reputation: 2134

What I have done in the past is when a search option is not being usesd pass in a null for its value. Then in your select statement you would do something like

 WHERE i.companyname = COALESCE(@CompanyName,i.companyname) 
   AND i.companycode = COALESCE(@CompanyCode,i.companycode)

What happens above is that if @CompanyName is null i.companyname will be compared to itself resulting in a match. If @CompanyName has a value it will compare i.companyname against that value.

I have used this way with 15 optional filters in a database with 15,000 rows and it has performed relatively well to date

More on the COALESCE operator

Upvotes: 3

Meff
Meff

Reputation: 5999

Dynamic SQL isn't the only way, it'd be better if you can avoid it with methods like: http://www.sommarskog.se/dyn-search.html

If you can't get the performance from the above method and go for dynamic SQL, do not allow the web-page to construct the SQL and execute it - you will end up getting SQL injected. Also avoid text strings being passed in, as sanitising them is very difficult. Ideally have the web page pass down parameters that are numbers only (IDs and such) for you to create the dynamic SQL from.

If you do decide to use dynamic SQL be sure to read all this: http://www.sommarskog.se/dynamic_sql.html

Upvotes: 2

Related Questions