How to create a mutliple search SQL statement where all the parameters are optional?

Question

I would like to know if there is any smart way of making a SQL statement for a search engine where there are 5 optional parameters. All parameters can be used or only one of them, or a mix of any of them.. This makes up to 3000+ different combinations.

The statement needs to be prepared to avoid SQL injections.

I've looked at this post, but it dosent quite cut.

What I'm looking for is something like,

String sql =SELECT * FROM table WHERE (optional1)=? AND (optional2)=? AND (optional3)=? AND (optional4)=? AND (optional5)=?

prepared.setString(1, optional1) and so on...

Answer 1

Use your java code to add the options to the where clause based on the presence of your arguments (their length or existence, whichever). That way if the optional parameter is not needed, it won't even be part of your SQL expression. Simple.

Answer 2

@a1ex07 has given the answer for doing this as a single query. Using NULLs and checking for them in each condition.

WHERE
  table.x = CASE WHEN @x IS NULL THEN table.x ELSE @x END

or...

WHERE
  (@x IS NULL OR table.x = @x)

or...

WHERE
  table.x = COALESCE(@x, table.x)

etc, etc.

There is one warning, however; As convenient as it is to make one query to do all of this, All of these answers are sub-optimal. Often they're horednous.

When you write ONE query, only ONE execution plan is created. And that ONE execution plan must be suitable for ALL possible combinations of values. But that fixes which indexes are searched, what order they're searched, etc. It yields the least worst plan for a one-size-fits-all query.

Instead, you're better adding the conditions as necessary. You still parameterise them, but you don't include a condition if you know the parameter is NULL.

This is a good link explaining it further, it's for MS SQL Server specifically but it's generally applicatble to any RDBMS that caches the plans after it compiles the SQL.

http://www.sommarskog.se/dyn-search.html