Seeing the code makes all the difference. It's not technically parameter sniffing.
That form of catch-all query does not perform well. Firstly the optimiser can't make accurate predictions with all of the constant comparisons scattered around. Second, there's no single optimal plan for this query. There can't be.
It works better with variables because the optimiser uses a different method to estimate row counts. It still doesn't perform optimally
My usual recommendations for a query like that - use dynamic SQL and only build in the where clause conditions you actually need. Use parameters and run the dynamic SQL using sp_execute_sql. The query will perform a lot better and the performance will be predictable.
Full outer join?
Distinct?
Usually needing one of those indicates that the DB design is questionable or the data integrity is missing. Needing both .... ???
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability