>>Hi Kevin
>>What if you want to switch the data to point to say Oracle or MySql?
>>
>>Bob
>>
>
>Stored Procedures take advantage of one of the fundamental advantages of a server-based database - data is processed on the server using the server's memory and only results sets are returned to the client saving network resources. This is a big deal on busy LANs, and a huge deal for web apps and web services.

This depends. The code to insert or update a table versus the code to call the stored proc to do that insert or update both require passing data from the UI to the database. The difference is minimal.

To me the biggest drawback to using SPs is it makes it very difficult to give the user a UI where they can do whatever query they want. Power to the user is what PCs were originally designed for.

>
>Plan appropriately first so you don't have to switch to completely different data providors. Oracle and Microsoft SQL servers scale from workstation to enterprise seamlessly so there's no real compelling reason to switch. If planning was bad initially or a company just wants to spend lots of extra money, then they can go ahead and switch.

With the appropriate construction, DLLs can and IMO should be the agnostic API. As I see it, the database should have data rules in it. The business logic should reside elsewhere. This would simplify switching among databases and still leave room for coding the data access layer to take advantage of any particular backend features.

I just recently have been working with a set of SPs written by a college professor-DBA-.Net "expert". Even so they were built in such a way that they severely taxed the servers resources - locking out other users and/or generating errors on the UI. SPs are not a magic bullet. besides tt is not an either or issue. It's about balancing both techniques to improve the ability to respond to business changes.