Database Programming: No Better Options For Optional Parameters

Well, the current leg of the great "optional parameter code-off" has been completed. You definitely want to party with me on a national holday. :) Previous posts in this thread (many filled with questionable advice, as least that coming from me) may be found here, here, here, here, here, and here.

The short depiction of the results is that, as Adam and Tony suggested in their comments, dynamic SQL provides the best performance in an optional parameter scenario.

Both flavors of my proposed syntax provoke table scans.  I was able to come up with a different approach that avoided the table scans, but it still produced a more complex query plan than the index seek provided by the dynamic SQL.

So, my thanks to all who posted on this topic, and my congratulations to those who insisted, all along, that dynamic SQL was the one true path for resolving this issue.

You were right!

     -wp