John,

>IMO, data access/manipulation logic (Selects, Inserts, Updates, and Deletes) belong in SP's. I support this argument on 3 levels: (security, performance, and flexibility)
>
>Security
>
>Even if you think security can be adequately managed in other ways, SP's offer an easier, less cumbersome alternative.

We handle security through our application. So where you argue that it's easier to manage just execute rights on SPs rather then tables, I would agree with you, but in our environment, I’m able to easily manage access to any portion of our application on a user by user basis. This approach being IMO a step easier and more intuitive then dealing with SP's.


>Performance
>
>Regardless of opnions to the contrary, SP's have a performance benefit over their rendered SQL counterparts.

This is the toughest area for me. First let me say, I totally agree from a performance standpoint, SPs are the way to go. What's difficult is what am I going to pay in performance on the development/maintenance side?

I would make a rough guess that as we move this system from VFP to SQL backend, we may end up with something like 1500 SPs if we took that purest approach. The majority of these would be created solely for use by a single module. We also have a very dynamic environment where we are frequently making updates and have a process for doing this without the need for taking the system down or having distribution hassles.

So for all the single use SPs, I see the following drawbacks :

The programmer must be developing/debugging in multiple environments ( VFP and Query Analyzer ),

Deployment adds another layer as every program would require both VFP and SP code updates. This also happens to blow our deployment process in that we would not be able to safely update SP code on the fly like we currently do with VFP code.

Also, if you do go the purest route, I’ve seen a tendency for people to write the generic routines in such a way as to optimize it’s reuse by adding additional fields and filters such that it can be used by a wider range of calling programs. Well, if my program requires only 5 of the 10 fields supplied in a generic SP, dragging those extra fields across the network just shot my performance in the foot.

So I see this being a performance vs development cost issue.



>Flexibility
>
>SP's give you the flexibility of mixing procedural and SQL code. Most importantly, SP's allow the code to be hosted in the DBMS itself. This means your logic is portable to any client that can connect to the server. This means that if the only thing you have access to is ASP and a script langage only, your data access/update logic is available. OTHO, if you embed this logic in middle tier components, you may not be assured of having the logic available, either becuase of technical or political/mgt reasons. Having said this, I do advocate that whenever possible, you build components that broker access to the SP's. i.e., don't use OLE-DB/ODBC directly.


Good point though I personally favor keeping as much business logic in a more robust middle tier language and leave strictly data manipulation on the back end. I may loose a little flexibility this way but nothing significant for my circumstances.


>This has been a "spirited" debate in the past. What I have outlined above, while a massivily cut-down version of things I have posted in the past, it is the core parts of my "Use SP's" argument. In the past 36 months, I have delivered either myself or part of a team 9+ mission critical apps that employ the use of SQL Server SP's exclusively.
>
>Whatever decision you make, think it through carefully.
>
>Good Luck..

I looked for any previous discussions on this here on the UT and did not find any. I’d love to read through the old posts if you happen to remember the subject or could give me a message #.

I really appreciate your feedback here. As general advice, and therefore applicable in many situations I agree with what you’ve stated. But our situation is somewhat unique, and as I consider all that has been said by you and others, then apply that to my environment, I'm thinking that the purest approach will not necessarily be best. I believe we’ll certainly be writing plenty of general use SPs, it just does not seem to me cost effective to require use of them in the case of single use SPs.

Thanks so much,

Anthony