Interesting article. Not sure I agree with you that static SQL is easier to maintain than dynamic. In my experience dynamic is usually called for mainly by search forms which require read only access - so if you put the user/login in the datareader role you can then pass over whatever you need in the query and its done - no changes required. With static you would HAVE to modify the proc.

Generating all the possible combinations can quickly become a burden. Steve and I have discussed building a tool that would generate the code, but even just assuming that all parameters are and'ed together if you have a lot of params it's huge and Im not sure would be very easy to maintain. Possibly it makes sense to convert the most common ones (or slow ones) to static, fall back to dynamic when that fails.

Beyond that, Im not sure it makes sense to build dynamic sql on the server, in the case of the search scenario anyway. This is easily done on the client and is definitely the way to go when you support all comparisions, not just equality plus logical and.

I'd like to see an assessment of the performance tradeoffs - be good to see just what each one costs.

Andy