Call me a purist, but Dynamic SQL gives me the shivers. There are clearly cases where it's unavoidable, of course, and thus the use of it in _GetPaged. I'm wondering, however, if it wouldn't be possible to limit these situations by boosting the interface for the _Find stored procedure.
One approach I've used in the past is to add the following parameters to a procedure:
[Field]SearchMethod
And then, in the where clause, implement something like:
@Field is null OR (
(@FieldSearchMethod = "=" AND @Field like '%\%%' AND Field like @Field) OR
(@FieldSearchMethod = "=" AND @Field not like '%\%%' AND Field = @Field) OR
(@FieldSearchMethod = ">" AND Field > @Field) OR
(@FieldSearchMethod = ">=" AND Field >= @Field) OR
...
Different datatypes may support different options, of course. For example, a date field may check equality against a calendar day if the time is not provided as part of the input.
Obviously this makes for a really complicated sproc - but that's why we have code generation, right? The main question then becomes: does this provide any performance, security or readability benefit over the Dynamic SQL approach? Since this will likely trip "With Recompile" the performance benefit may be negligible. Readability also seems of minimal benefit due to the complexity of the where clause. At minimum, however, this is more secure.
Thoughts?
Jeremy