It can be done with SP, if you overload the parameter statement. Or, you can create different SP's for the different types of criteria.

Of course, the same can be said with RV's. You can have multiple parameters or have multple views. There should be finite alternatives with respect to the fields that can be used in a query. For those fields the user does not explicitly provide a value, a wildcard is used.

From the POV of fields that get returned, depending on the context, one can simply ignore specific fields. This however, gets into the problem of returning more data than is necessary.

Still, I do see you point about SPT being more agile in this regard. For the most part, I do agree with that premise...

I guess the real issue is how much of your data access strategy do you place in your application code as opposed to a SP's that lives on the database server?

Imagaine a DBA who is in need of opimizing and tuning the database. S/he might start with taking an inventory of the SP's, to see what queries are run against the backend. This is one of the reasons why rendering SQL from a client is not a great idea. This is why many companies do not allow this sort of thing to occur. For best performance, DBA's lock down what can be requested from the database.

If for some reason, a query could potentially return 100,000 rows, unless it is a specific sp that it makes sense to do that sort of thing (report, data extract, etc...), a sp can simply elect to return only the top 1000 rows and inform the user of what happened.

To me, these are the bigger issues. The issues of where the SQL statement resides and how it gets rendered, in the big picture, is a rather superficial thing. It is the surface matter. It is the potatos, not the meat. Trying to think of another cliche...< bg >...