我想检查是否有preferred设计模式，实现与对数据库表的多个可选参数，其中对数据库的访问应该通过存储过程只是搜索功能。

I would like to check if there is a preferred design pattern for implementing search functionality with multiple optional parameters against database table where the access to the database should be only via stored procedures.

目标平台是.NET中使用SQL 2005，2008年的后端，但我认为这是pretty的一般性问题。

The targeted platform is .Net with SQL 2005, 2008 backend, but I think this is pretty generic problem.

例如，我们有客户表，我们要提供搜索功能的用户界面不同的参数，如客户类型，客户状态，客户Zip等，所有这些都是可选的，可以在任意组合中选择。换句话说，用户可以通过仅customerType或customerType，customerZIp或任何其它可能的组合进行搜索。 有几个可用的设计方案，但他们都有一些缺点，我想问一下，如果有其中一个preferred设计或是否有另一种方法。

For example, we have customer table and we want to provide search functionality to the UI for different parameters, like customer Type, customer State, customer Zip, etc., and all of them are optional and can be selected in any combinations. In other words, the user can search by customerType only or by customerType, customerZIp or any other possible combinations. There are several available design approaches, but all of them have some disadvantages and I would like to ask if there is a preferred design among them or if there is another approach.

生成SQL，其中动态子句SQL语句在业务层的基础上，从UI的搜索请求，并把它传递到存储过程的参数。喜欢的东西@Where ='在这里CustomerZip = 111111 里面的存储过程生成动态SQL语句和sp_executesql的执行。 缺点：动态SQL，SQL注入

Generate sql where clause sql statement dynamically in the business tier, based on the search request from the UI, and pass it to a stored procedure as parameter. Something like @Where = ‘where CustomerZip = 111111’ Inside the stored procedure generate dynamic sql statement and execute it with sp_executesql. Disadvantage: dynamic sql, sql injection

实现与多个输入参数，再presenting从UI搜索字段的存储过程，并使用下列建设选择只记录所请求的领域在哪里声明。

Implement a stored procedure with multiple input parameters, representing the search fields from the UI, and use the following construction for selecting the records only for the requested fields in the where statement.

WHERE

        (CustomerType = @CustomerType OR @CustomerType is null )

AND      (CustomerZip = @CustomerZip OR @CustomerZip is null )

AND   …………………………………………

缺点：对SQL的性能问题

Disadvantage: possible performance issue for the sql.

3.Implement分开存储过程的每个搜索参数的组合。 缺点：存储过程数量将增加的搜索参数，反复code迅速增加。

3.Implement separate stored procedure for each search parameter combinations. Disadvantage: The number of stored procedures will increase rapidly with the increase of the search parameters, repeated code.

推荐答案

这是最好的文章描述了如何执行此操作在SQL中的细微的性能影响：的动态搜索条件在T-SQL通过厄兰Sommarskog 。它涵盖了所有的方法，并给出各种方法的优点和缺点非常详细。

This is the best article describing the subtle performance implications of how to do this in SQL: Dynamic Search Conditions in T-SQL by Erland Sommarskog. It covers every method and gives PROs and Cons of each method in great detail.