Paul White NZ (3/24/2010)It's really not so much that. In the 'bad plan' you will see a Filter operator as one of the inputs into the concatenation operator. ...

Thanks for the response Paul. I had actually broken the statement into two, as you suggested, as a work around for now.

A little background on that syntax: we used to...

Thanks very much for the feedback. I like this explanation a lot, it seems to fit the bill. I also read the following:

http://www.sqlmag.com/article/sql-server/-using-the-recompile-query-hint-to-solve-parameter-sniffing-problems.aspx

This also seemed to fit very well to...