>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.
>
>That first statement is a matter of opinion. Some may feel that way, and others may see that as a non-issue. As for the rest of this point, check out my other post and my blog entry about the mechanics for an UPDATE stored proc that can handle both NULLs and optional parameters.

I've read your blog and its certainly an improvement over the first attempt. There is a tiny concern a user want to use a value, you've assigned as the default parameter value and not able to replace the value, but I can live with that. Now these type of
require a generator for SPs as you typically would not want to write them from scratch nor maintain them this way. Do you agree with the assesment that *IF* you want to use update stored procedures they have to be 'generated' and constructed the way you described in your blog ??

But unless the whole world accepts this update routine as the gold standard for updating, in practise update SPs still suffer from all types of problems. Whole tribes have written SPs with all kinds of limitations in there (Until very recently, since you came up with this solution very, very recently, yes, in your update SPs as well) and my bet is that many do not use a code generator as well.

This is why I said in the original point 'Many implementations are not able...' It is not enforcing you to do it in the right way.


>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.

>You can implement searching and scoping with all sorts of parameters - you don't need to pass strings to append to SQL statements in a stored proc to get additional capabilities. You can now pass true XML to stored procs, and you can use CASE and other statements inside stored procs to get "flexibility".

Yes, again, 'YOU CAN', but in practise SP leave too many things open to the developper. And you and I know that in practise, though code injection free SPs are possible, it is not uncommon for SPs which task is to query data to be suspectable to code injection. With remote views there is zero chance for that.

>(For the very first time, I think I see why you're saying at least a few of the things you're saying. Look....yes, many people don't use sprocs correctly. But that doesn't mean the methodology is wrong, it's just a case of inexperienced people who don't have years under the belt, using them.)

Well, that is a matter of perspective. If a methology allows beein abused to an extend that a very large percentage is not using it in the correct way, you should seriously doubt the methodology. The methodology itself should protect you from that. An example again is remote views vs code injection.

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

>Once again, there are things that can be done in some instances to address this.

See below.

4a. Querying SPs are a PITA, unless you allow for code injection, each time as a developer wants to use different criteria to do his query for their application, the developer needs to ask the DBA (if he is the gate keeper for SPs) to adjust or create a new SP. Now there are two people involved in getting this task done and the overhead of such a simple request is not making this productive, esspecially if things need to be finetuned in the development cycle. The SP itself is 'hand made' so for each possible query that is fired at the database the DBA will need to guard the SPs. This is severely limiting the applications to have an extensive set of user definable reports. Applications that need to be able to ad-hoc query the database, require a more open query possibility to the database.

>5. They are a PITA when doing multi table transactions. For example try to wrap an order and multiple orderlines into one transaction.
>
>I have to admit, I honestly do not understand why you think that can't be done, or why you believe they can't be done. Between transaction objects, error handling capabilites in SQL 05, etc....this is hardly a PITA. If you really and truly feel this is an issue, I'll post a small sample to demonstrate this.

Please give me an example of the case above and I'll give a reaction on. Note that I don't say it is impossible (as about nothing is impossible, only that it is a PITA). I guess you want to pass the whole order as XML and process that in one SP, right ??

>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.

>Once again, check out my post and blog entry about unneeded parameters. It's important to distinguish between the way people are doing things, versus the capabilties of the tool.

Your blog does not address this. The unneeded parameters or unpassed parameters are still processed in the update statement.

>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.
>
>OK, fine, they work well for you and others. But how is this a criticism of stored procs?

Ok. Compare this to how you have to handle SPs. You have to maintain each call when the signature of the SP changes, so you'd probably want to have this handled is some data handling class, to make sure you only have one or very few instances where the SP actually is called.

It is the simplicity and the abstraction of the remoteviews that give you the advantage. The question is why you ever want to use SPs in case when you can have remote views. On another note, you can call a SP for getting the data into the remote view and update through dynamic SQL.

For example, in my application I can go to the build in command window, do a USE, BROWSE and update the records in there... very easy and transparant. Now compare that to a situation where I use SPs exclusively.

CONCLUSION

Bottom, line what do SPs gain you? I don't see too many advantages and see quite a few possible problems. Personally I think the decision whether to use SPs as a starting point for a particular project is more determined by the following rather than any 'Technical arguments'

- The requirements of the client.
- The experience and preference of the developer.
- The nature of the application.

So, if you are a independed consultant working for enteprises with large and critical databases that are used by many applications guarded by a small army of DBAs, SPs might be a good policy from the client perspective. Esspecially in cases where the pool of developers working on the applications is dynamic and changing very frequently.

In other cases where you as a vendor of a database application and are in control of the database, I only see limitations of the exclusive usage of SPs. SPs do have its place, but only where it makes sense.

In general SPs just add another layer of complexity of distributed responsibility. Changes to a SP often requires work on the database and the program (sidenote: SPs are not tracked in source control), not seldomly by two different people. This might seriously hinder development in general.