动态搜索条件的***来源:

best source for dynamic search conditions:

Erland Sommarskog 的 T-SQL 中的动态搜索条件

对于是否可以使用索引，您如何执行此操作有很多微妙的含义.如果您使用的是 SQL Server 2008 的正确版本，您只需将 OPTION (RECOMPILE) 添加到查询中，并且运行时的局部变量值用于优化.

there are a lot of subtle implications on how you do this as to if an index can be used or not. If you are on the proper release of SQL Server 2008 you can just add OPTION (RECOMPILE) to the query and the local variable's value at run time is used for the optimizations.

考虑到这一点，OPTION (RECOMPILE) 将采用此代码(其中没有索引可以用于这种乱七八糟的 OR s):

Consider this, OPTION (RECOMPILE) will take this code (where no index can be used with this mess of ORs):

WHERE
    (@search1 IS NULL or Column1=@Search1)
    AND (@search2 IS NULL or Column2=@Search2)
    AND (@search3 IS NULL or Column3=@Search3)

并在运行时对其进行优化(前提是只有@Search2 传入了一个值):

and optimize it at run time to be (provided that only @Search2 was passed in with a value):

WHERE
    Column2=@Search2

并且可以使用索引(如果您在 Column2 上定义了一个索引)

and an index can be used (if you have one defined on Column2)