>>---Using stored procs is considered a "best practice".
>
>A mix of stored procs and parameterized ad-hoc sql and business logic in the middle tier is IMO "best".

Thanks, this was the best way to say what I wanted to say :).

I've done some pretty heavy SP in my time, and it worked great - because the task at hand was to do some heavy data crunching without any need for anything outside the DB.

OTOH, I've seen some SPs where I had my serious doubts whether they'd be the right approach. It was about a string search, where there were five almost identical SPs, to handle the case when there was one word to search for, or two, ... or five. Which is IMO ridiculous, I'd rather have just one SPT call composed programmatically (but then the client side was done in Java - and I wonder whether those guys were able to compose a SPT call on-the-fly). For more complicated searches, with more fields involved, the TSQL just doesn't provide the flexibility, not that I know of, to compose an ad hoc query, not such that we can easily knit in 10 minutes in VFP.

I'd conclude the answer to "SP - yes or no" is the classical "it depends".