Maximum amount of where clauses in stored procedure

Me and my colleagues have a question regarding SQL Server 2008 query length and the SQL Server Optimizer.

We are planning to generate some stored procedures that potentially have a lot of parameters. Inside of our stored procedure we will simply select some values from a table joining other tables.

Our stored procedures will look like this

CREATE PROCEDURE QueryTable
@Parameter001 nvarchar(20),
@Parameter002 int,
@Parameter003 datetime,
@Parameter004 decimal(11,2),
@Parameter005 date,
@Parameter006 varchar(150),
@Parameter007 int,
@Parameter008 decimal(5,2),
@Parameter009 nvarchar(10),
@Parameter010 nvarchar(200),
@Parameter011 nvarchar(50) --,
--...and so on, there are probably 50 to 100 parameters here

AS
BEGIN
SET NOCOUNT ON;

SELECT ID, COL01, COL02, COL03, COL04, COL05 from TestTable T
LEFT JOIN AnotherTable A On T.SomeColomn = A.SomeColumn
LEFT JOIN AThirdTable ATT On A.ThirdTableID = ATT.Id
--and so on, probably 5-10 Tables joined here
WHERE 
      T.Col02 = @Parameter001 AND
      T.Col05 = @Parameter004 AND
      ATT.SomeColumnContainingData = @Parameter027
      A.AnotherID = @Parameter050
      --prob开发者_JAVA技巧ably 50 to 100 conditions here (Number of conditions equals number of parameters)
END
GO

Our questions: Is there a limit on the amount of where-conditions that the Query Optimizer and the SQL Server Cache can take into account? If there is not such a technical limit, is there a best practice on how many conditions can and should be used in such cases?

A limit of the number of WHERE clauses is not going to be your problem.

Parameter sniffing and poor (or incorrect) query plans cached might be.

This can be somewhat avoided using OPTIMIZE FOR

Obviously, the less complex you can make the WHERE clause, the better.

The SQL Server Books On Line has a list of implementation limits, including aspects of queries. This is the list of SQL Server 2005.

In case anyone is interested...

We solved this issue by generating dynamic sql that is executed on the server. In this way only relevant where clauses are used and the statement is relatively short.