Please check this SP. Does it look familiar?

Easy to implement. Easy to support. Only minor problem – it never uses indexes. Never. Period.

Let’s check that with simplified example

For us, who came from imperative development world, it does not look right. How come that SQL Server cannot do the simple optimization and determine, that parameter is not null. Well, it does not. It does not know this during the compilation stage so it would scan entire table and apply both predicates to every row. Even if first predicate needs to be checked only once.

So how to deal with that? The most optimal way is to specify the list of all parameter combination. Something like that.

Every select would be optimized. Brute force approach will require 2^N selects where N is the number of parameters, which is introducing some limitations with N > 1..3. In real life it could be slightly better because if you can force index seek with one predicate, you should not really worry about others. So it could be done in the next way:

Still requires quite a few different selects and hard to maintain.

Another solution is dynamic SQL:

It would lead to recompilations of dynamic SQL for every SP call. Well, this is the downside of that method. Fortunately there is the good chance that plan would be in the cache especially if SP is called often enough so compilation would not be expensive.