chris-945648 (5/19/2010)
Before you went through all of the rigmarole of creating a stored procedure that constructed dynamic sql, did you try a combination of adding "WITH RECOMPILE" or using OPTION(OPTIMIZE FOR UNKNOWN) in the select itself?
And why would that make a difference? SQL Server still has to come up with an execution plan for all the statements in the WHERE clause. I've seen enough procedures with multiple optional parameters and developers trying to create "clever" methods of combining them into one ugly TSQL statement, to know that dynamic SQL cannot be avoided. In SQL Server 2005 and SQL Server 2008 you can also prevent the problem with broken ownership-chaining in dynamic SQL by signing the procedure. Problem solved.