Implementing search functionality with multiple optional parameters against database table

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.

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

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.

1. 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

2. 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   …………………………………………

Disadvantage: possible performance issue for the sql.

3.Implement separate stored procedure for each search parameter combinations. Disa开发者_如何转开发dvantage: The number of stored procedures will increase rapidly with the increase of the search parameters, repeated code.

---

The Query Object pattern.

---

Method 1: dynamic SQL can take parameters, its pretty trivial to do and pretty much eliminates the risk of SQL injection. The best argument against dynamic SQL is how non-trivial statements can require some complex logic to generate, although this is a non-issue too if you're using a decent ORM.

NHiberante and LinqToSql construct dynamic SQL behind the scenes, and they aren't riddled with security holes. In my opinion, you're best considering one of these two technologies before rolling your own DAL.

Method 2: I have personally used method two in the past with no problems. You commented on the "possible performance issue for the sql", but have you profiled? Compared execution plans? In my own experience, their has been little to no performance hit using the @param is null OR col = @param approach. Remember, if it takes you 10 hours of developer time to optimize code to save 10 microseconds a year of execution time, your net savings is still almost -10 hours.

Method 3: Combinatorial explosion. Avoid at all costs.

---

I posted this as a comment, but I realized it should probably be an answer.

It's not good to write predicates as WHERE @Param IS NULL OR Column = @Param, because the optimizer usually considers it to be non-sargable.

Try this experiment: Take your most populated table, and try to query just for the Primary Key field, which should be your clustered index:

DECLARE @PrimaryKey int
SET @PrimaryKey = 1

SELECT CoveredColumn
FROM Table
WHERE @PrimaryKey IS NULL
OR PrimaryKeyColumn = @PrimaryKey

SELECT CoveredColumn
FROM Table
WHERE PrimaryKeyColumn >= ISNULL(@PrimaryKey, 0)
AND PrimaryKeyColumn <= ISNULL(@PrimaryKey, 2147483647)

Both of these SELECT statements will produce identical results, assuming that the PK column is a non-negative int. But pull up the execution plan for this and you'll see a huge difference in cost. The first SELECT does a full index scan and typically takes up about 90% of the query cost.

When you want to have optional search conditions in SQL, and you can't use dynamic SQL, it's best for performance if you can turn it into a range query instead using ISNULL. Even if the range is huge (literally half the range of an int here), the optimizer will still figure it out when the optional parameter is used.