Rory Primrose

The more I look at this, the more I think that parametised dynamic sql using sp_execute_sql. It is supposed to perform well because its execution plan will be cached, provides flexibility for modifying the filtering and ordering statements and avoids SQL injection attacks.

The approach you outline (FieldA = @FieldAValue OR @FieldAValue IS NULL) which I have used myself in the past is usually terrible for performance. There are some other approaches you can take without using dynamic SQL - like a bunch of specific queries with good execution plans for the common cases, and something like you suggest as a fallback, but this can give wildly different response times based on what parameters you pass. If you use something like this approach make sure you perf-test the heck out of it with a realistically sized database and production-like hardware if possible.

I used to think dynamic SQL was totally evil, but situations like this with paging, sorting and querying over a variable number of fields, which are in turn potentially spread across multiple tables...makes me think it does have a place.

I have always been against dynamic sql mainly due to caching of the execution plans. But after reading how sp_execute_sql caches the sql statements, that really kills off that excuse.

There is still a minor disadvantage. With the same search procedure being used to search across different fields, that would still result in multiple generations and caching of execution plans. That being said, it's really only a problem for the first hit through the application processing.

I did look at the RANK() OVER as a solution, but that was truely terrible performance with more than a couple of columns.