Implementing search functionality with multiple optional parameters against database table

Question

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. Disadvantage: The number of stored procedures will increase rapidly with the increase of the search parameters, repeated code.

Answer 1

The Query Object pattern.

Answer 2

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.