By the way Jon, I think I understand why you have scans everywhere while trying the method in my post above: it's a bug in my code. Indeed, there is still the possibility that all of the parameters are NULL, and SQL compiler takes it in account (in that case, the "WHERE ProductID IN ()" subquery gets inhibited, thus fetching all the rows, and then the scan is the most stable execution plan in all combinations including this latter one.

Try adding this in the main WHERE clause:

... AND NOT (@Name IS NULL AND @Colour IS NULL AND /* rest of the parameters */ )

This is saying explicitly to the compiler that "among those parameters, at least one is mandatory".

I observed the compiler really understands it and removes the scan if this is the case here.

If it changes your execution plan the way you expect, I hope I will have showed how scalar expressions can really drive execution plans, and consequently what you can do with such an ability. At least you would have seen by yourself the disastrous result of one very specific use case immediately, while using dynamic queries you would have to hit that case to see it; and that is what I am trying to explain. 😉