I've got fundamental issues with using stored procs (e.g. not cross platform, database depended, so not flexible). I'd rather use SPT for the things I'm doing.

I find in the apps I'm building I'm doing a lot of SQL construction on the fly (which is rooted in the high level of data driven validation rules, queries, and business logic) in which case SPT is the only choice. I Try to limit the SPs to a minimum, (avoiding the database to become part of the products logic or applications specific data access).

I'm also using a lot of remote views in which case of course you can use typed datasets.
It seems that in .NET I'll have to deal with two different dataacess methodologies, typed and untyped datasets.

Walter,

Until a year and a half ago, I felt the way you did. I strongly preferred to build SQL statements via SPT. It turns out my conclusions were based on limited knowledge of what T-SQL and PL-SQL could do. I've spent the last year and a half researching how the things I used to do via SPT (especially dynamic construction of queries) can be done with stored procedures. Not only have I found that many things are possible, I also learned some new techniques as well.

I just came off a project where I built financial statements. I wrote a stored proc to bring back 5 result sets for the statements. I can interactively test using Query Analyzer, passing a set of parms that would represent any real-life situation, and often I can make changes without needing to touch/recompile the app. I'm not saying it's a pure silver bullet, but my overall productivity has increased by developing something that's largely client agnostic.

True, stored procedures themselves are not always portable, especially if you wind up using some of the more advanced features of the database's language. For instance, I've found COALESCE to be a great help, though I don't know if there's a COALESCE in other products. (Maybe there is...I've never verified). To a certain degree, one can ease the blow by placing those statements in UDFs. That's not a full solution, but it can help.

Unless there's a situation where the app doesn't know the structure of a result set until runtime, typed datasets are the way to go.

Good discussion...
Kevin