From the top of my head... I'm sure someone could add others.


The cons of SPs.

1. Update routines are complex and non - trivial. Many implementations are not able up update the table with .NULL. values or if they do, require all fields to update to be passed having serious concurrency problems. OIW, they have serious limitations in updating records.

2. As opposed to remote views, the developer needs to have a list of all update parameters and pass them at the right position, not making updates transparant nor easy.

3. As opossed to remote views, SPs in practise tends to be more suspectible to code injection since in practice strings are passed and added to SQL statements to provide certain flexibility in searching and scoping. Remote views, are protected from this kinds of code injection.

4. They are a no go when your application needs to create SQL statements on the fly (application Query engine).

5. They are a PITA when doing multi table transactions. For example try to wrap an order and multiple orderlines into one transaction.

6. Dynamically created SQL can be written more effecive as opposed to using CRUD Sps as the latter tend to be gereneral purpose where unneeded parameters need to be passed and processed. Dynamic SQL tends to be far more granular, esspecially in concurrency issues.

7. SPs are DMBS specific and their implementation seldomly is ANSI compliant. So if you plan to support multiple databases, you'd better think twice to use SPs. Remote views and/or using SPT might be a better choice.

8. Remote views, which create efficient dynamic SQL, provide an easy and totally transparant way of updating your back-end database, just USE it and TABLEUPDATE() and VFP takes care of the rest. Remote views (or updatable SPTs) provide a great way to provide transparancy between local and remote data.


Mis-used Pros of SPs

1. Performance... In most cases there is no measurable difference between dynamic SQL and SPs. Execution plans are reusable in both cases. Also reusing executionplans can also be a disadvantage. In some cases you want to execute your SP with a WITH RECOMPILE argument to force a new executionplan based on the different selectivity of the parameters. Also, keep in mind that nowerdays the time to create an executionplan becomes less and less significant compared to the actual execution and networktraffic as databases grow and hardware becomes cheaper and faster.

2. Remember that it is perfectly possible to write a whole SQL script as you would in an SP and pass that as dynamic SQL. Personally I've used this technology to execute large SQL scripts defined on the client in a VFP metadata table. IOW, you don't need SPs to execute SQL scripts.

3. You're able to do a lot of processing in one round-trip. Really is a consequence from 2. Nothing really stops you from sending a whole SQL script doing exactly the same.


Other remarks on the characteristics.

With SPs the control of database access is centralised through the SPs. This might be preferable in some types off applications where security and authorisation is critical and cannot be handled through the client or middle-tier.

In other cases this is frustrating when your application wants to extract, update information in a way that was not anticipated upon. When your application needs free access to the underlying tables using SPs is a very frustrating excersize.


>I'm gonna put this in my blog....basically, in no order of importance:
>
>- They provide an application/client-agnostic API for accessing the database.

Which can be both advantage and disadvantage, depending on how you look at it.

>- Having an application-agnostic API for the database can really become beneficial in a corporate environment with several applications and developer teams.

But can become a pain in the but for application specific databases which is maintained by the vendor.

>- If I had a dime for every time an I.T. manager breathed a sigh of relief when I told him/her that something could be added/changed/fixed simply by tweaking a stored proc (as opposed to having to update an application DLL), I'd have plenty of dimes.

What does this say ?? As oposed to something that could be fixed by addressing it in a remote view or metadata table ?? Not sure how to value this argument.

>- I can abstract out and establish an API and build stored procs independantly of the application, and in many instances can make changes without disrupting the development environment

Which is not exclusive to SPs. Remote views, SPT solutions don't have to be build into the application. I think Andy kramer and Marcia Akin have build a framework for handling this in even a more transparant way.

>- The new tools for building custom database unit tests in Visual Studio Team Edition for Database Professionals are outstanding. (If anyone is interested in building unit tests for stored procs, you HAVE to look at this product. It is fantastic).

Are you saying that unittesting a client specific code for data access layer is not possible? How about those poor developers not using Visual Studio Team Edition??

>- It's much more difficult for a DBA to manage database code if it's residing inside application logic

The question is if a DBA should be responsible for that. Isn't the vendor??

>- SQL 2005 contains many new language features that are best utilized inside stored procs.

Example please. Can't they be used in a SQL Script passed as SPT ??

>As I've said before, I use them as a strong starting point, unless there are good reasons for not doing so. In general, I believe that all external access to a database should occur through stored procs.

I've not seen a compelling reason here to use them as a startingpoint, and would regard this as a bad advise esspecially in the mom-and-pop and standard software packages scenarios.

>Clients WANT me to use stored procs. I work with at least a few new I.T. teams each year, and I can't think of one that didn't want to use them. And when the ultimate decision is left to me, I use them on a regular basis.

Your personal experience, does not match mine, mainly because my market it different. My clients want a working product whether with or without SPs.