Good discussion.

We have found that a combination of SPs and SPT works best. For example, execution plans showed us that complex TSQL SPs, in some cases, were inefficient and bogged the server with CPU spikes.

My framework utilizes a VFP second tier COM+ Application Server which communicates with SQL Server by passing through dynamic SQL. In the case of reports, it accepts parameters from the client and then builds the SQL. A client UI can then specify a dataset request based on filtering, sorting, and grouping options to be passed through. This cannot be handled gracefully in SPs. In fact, there are three SQL Server consultants from Microsoft sitting next to us (trying to fix a failed java project <g>) who praised our approach.

Don't get me wrong. We use SPs extensively, but have found unbeatable performance when used in combination with SPT. In many cases, we use SPs to get a bunch of fast intermediate result sets which are then joined in the VFP COM+ Server to produce the final result set sent to the client.

>
>If I were doing transaction stuff for a bank, I'm almost certain I'd want SPs- not least to centralize control over transactions in both senses of the word(!) where SNAFU could result in disaster. but I'm sure you're doing lots of batch stuff and mostly inserts rather than cycling edits... yeah, I'm sure SP makes a huge amount of sense.
>
>Would that translate to an uncritical belief that SP is simply "the best"? I'd be ashamed if I allowed myself to think that way, and I can see you would too. Horses for Courses and all that.