You might think I'm obsessing over this, and you may well be right, but I wanted to make one more comment about handling optional parameters.
The issue with the syntax I initially proposed was that it didn't handle nullity in the database properly. If any of the tested columns are NULLable, then, as far as our current research is concerned, dynamic SQL is the one, true path.
However, if all of the columns being tested in the database are declared NOT NULL, but the parameters themselves are NULLable, then I believe a modified version of my proposed syntax will work:
WHERE y1 = COALESCE(@p1, y1)
AND y2 = COALESCE(@p2, y2)
AND y3 = COALESCE(@p3, y3)
AND y4 = COALESCE(@p4, y4)
This produces identical results (and an identical query plan) to dynamic SQL if the conditions above are met. It's certainly a niche application rather than the bold generic statement that I initially hoped to make, but if I'm correct (Adam? Tony?) then I will at least have salvaged a small shred of this work.. and my dignity. 🙂
Comments are encouraged, as always. Thanks for tolerating my focus on this issue.