Nice recommendations. I am in the process of doing some sql "stuff" for my thesis and I was contemplating using stored procs to improve performance. The current method of using XML Web Services with native sql is too slow for the massive amounts of data I need to process. So I thought I'd look into SQL Server 2005 which can "apparently" integrate Web Services. How much of this is true is still unknown to me. But thats an interesting approach don't you think?

At the job where I learned SQL, we used stored procedures and triggers a lot. The system included a database core with a suite of applications - some web-based, some unix, some (old) Mac - around it. The apps were written in C, C++, Perl, SmallTalk, and probably a few other languages I've forgotten. AppleScript, too.

The idea there was to embed (much of) the business logic into the database, so that all the applications, wherever hosted, would follow the same logic; also to avoid duplication.

People talked about efficiency, but I never saw any evidence of it, either way.

A final advantage of this approach was the deployment issue. Some of our apps needed to be distributed to users, or users needed to be forced off the system to update an app. When the logic of a stored procedure needed to change, we could just change it, live, and everyone would immediately see the benefit. Or bug.

I was comfortable with this system and these justifications for stored procedures, and I feel that it worked pretty well for us. But as you say, every project is different; I wouldn't expect this approach to apply to all cases.

> I was comfortable with this system and these justifications for stored procedures

Sure; back then a web service wasn't an option. But based on your description it'd be a good candidate for that approach today, particularly with all the different platforms.

I still maintain that stored procedures are a terribly limited place to build an API, though.

> Web Services with native sql is too slow for the massive amounts of data I need to process

That's understandable. Are you returning lots of rows or just processing a lot of rows to generate smaller result sets?

>So I thought I'd look into SQL Server 2005 which can "apparently" integrate Web Services

Interesting, I wasn't aware of that; the feature is called "HTTP Endpoints"

http://www.developer.com/services/article.php/3390241

Given the fact that most databases used today are just big boxes filled with crap, I must grudgingly agree with most of your observations. I do however, wish there were decently designed databases that enforced relationships, encapsulated rules and required domains to be specified.

I think one of the great things about stored procedures that shouldn't be readily discounted is the fact that you can limit a user account to only execute stored procedures and furthermore you can specify exactly which stored procedures the account can execute.

The alternative is to give the account select, insert, delete, and update permissions. And that means that whoever can get access to the SQL user can do whatever he wants instead of whatever you want him to do.

I'm not a DBA so there may be some way to lock down user accounts that I'm not aware of, but this feature trumps all of the supposed performance and code cleanliness arguments. They're gravy in my book.

"I think one of the great things about stored procedures that shouldn't be readily discounted is the fact that you can limit a user account to only execute stored procedures and furthermore you can specify exactly which stored procedures the account can execute."

Maybe I am unique but I have not written a program/web page/what ever in the past 10 year where the database built in logins were used for end user authentication and authorization. I have long switched over to the application controlling access and usage and the user never seening the database access accounts or passwords(when needed).
From an end user(exclude dba and dev tools) stand point the only thing I have even seen in that time period are report type tools that allow you to build custom reports and I would not like to have to make the users use stored procedures in thoses tools to access there data ; and thoses accounts are usally limited to read-only.

The problem with using stored procedures in the way you mention is that you don't have the fine granularity that you get with using views and granting select/update/delete to tables or columns as/if needed. With most implementation of SP once you get to the SP you can enter any information as the parameter and if you are doing all CRUD work with SP you have to have SPs for all tables which would allow you to do almost anything you could do with SQL. No real additional security, and administrative wise it is alot easer to handle roles linked to views and tables then all the extra baggage the doing all CRUD work in SP brings.

Egads, I wasn't talking about having actual logins for the users of an app. I was referring to the logins that web apps run under.

I agree with you Bill, but I think I understand what Will is getting at. I've worked on many apps which had both SP and raw SQL interfaces. This is usually necessary because the demands for ad-hoc reporting are too broad and general to be encapsulated in any set of stored procedures.

So what you end up with is, in many ways, the *worst* of both worlds. Raw SQL access to the underlying tables for the developer(s) writing the reporting code, and "abstracted" SP-only access for the developer(s) writing the application itself.

Hmmm

I agree that wrinting "Mindless" SP is NO fun. But i also see the advantages of them.

- Preventing your users from accessing the Tables directly
- "Automatic" Imunity vs SQL-Insertion Attacks
- Precompiled Code

The only problem is writing them. Writing "simple" SP is repetative, mindless and boring work. If anyone objects... Thats your problem. I rater write some software to create those SP from the Metadate that the SQL Server offers me. This is easy once you got the hang of it and it will allow you to create most "basic" SP you need. The SQL-Server knows all the information you need to know to start pumping out those SPs And Automatic generations of SPs like Get_Custumer_Orders isnt that hard. The key here is that you need to properly design the DB. This can save you tons of work later.

I am a lazy Bastard and i see that as a good trait :)

Heiko,

I agree that code generation is a good way to go here. But items #2 and #3 on your list aren't really advantages:

#2 it is possible to write procs that are vulnerable to insertion. If you use parameterized SQL, which you should ALWAYS be doing, you're basically protected from injection attacks. So it's kind of a wash.

#3 parameterized SQL is hashed and compared to a precompiled execution plan after the server has seen that query more than once. All subsequent uses are precompiled. The only disadvantage is slightly more data sent over the wire in the initial request.. eg, "SELECT * .. [500 more chars]" versus "MyStoredProc(param1, param2)"

As for #1 I personally feel procs are a bad place to build access control, but it is a valid use.

> I am a lazy Bastard and i see that as a good trait :)

LOL, yes, lazy is good. If it's the right kind of lazy..

http://www.codinghorror.com/blog/archives/000237.html

I just took a second to read the Article... (So far i only read the Blog) and i must say I didnt know that SQL is also storing an Execution plan.

But i like working with a robust DB. That means a DB that isnt just a loose collection of tables but has some well defined contrains. And thse informations "belong" into the DB for me. I hate doing work twice so you wont find me coding the same contrains on all levels of my application

In number #2 I quoted "automatic" since those SP are also accessed by my (generated) DAL. So far i find it easier to generate then to type by hand. (I really enjoy generating code)

Anyway i like the Article you posted :) And beeing lazy can be a lot of work if its done properly

I'd like to add several clear cut benefits to using stored procs instead of dynamic SQL. I think my points have not been discussed much so far.
The problem of complexity has been solved many years ago. Put simply, Divide and conquer. By dividing any complex (coding) task, it becomes far easier to solve it.
This means that Stored Procs section off your application so that it is easier to test and easier to design. Stored procs provide small logic blocks that can be tested independently of compiled code. This separation of executable code from Database code makes debugging FAR faster and easier.
Just like graphical artists can improve a page because we now separate off the HTML from our actual code, Stored procs allow developers to work on the data side of their apps separately from their executable code. Mixing things, then, is in general, a limiting and bad practice.

If a program generates dynamic or concatenated SQL, you don't really know exactly what SQL is running unless you step through the exact code with the exact values. Sectioning off part of the work into a stored proc keeps things much simpler. It is possible to use the MS SQL profiler to debug dyncamic SQL but it requires DBA level permissions which are nearly always denied to developers.
Another benefit to Stored procs is that SQL is simply terribly messy to embed directly into code pages. At any of the large corporations I've worked at, a single table frequently had over 30 columns; some had 200 columns. Often, insert statements required values from other supporting tables so the SQL statements would be endlessly long. To embed that quantity of logic and SQL into a code page is a real mess.
And, update statements or select statements can take 4 or 5 times as many lines as insert statements.
It's far easier and more maintainable to view and work with SQL separately instead of having it mixed with code.
Just as in the "old" days when ASP was mixed with javascript and HTML, it's FAR easier and cleaner to avoid mixing languages inside a code page. As most of us know, In the "old" days, ASP mixed with javascript, mixed with XML, mixed with html created a true mess of spaghetti code. The same principle holds on mixing SQL with code.

Also, using stored procs allows different applications and developers to use common database functions. SP's are a simple way to encapsulate common sections of business logic that are easily reusable despite language and platform differences.
I'm not at all convinced that stored procs are any faster for simple insert or select statements though. But in terms of keeping coding more organized, in terms of debugging, in terms of building larger more sophisticated applications, Stored procs are practically essential. If you can't agree with these points, ask most any DBA what he thinks. DBA's work daily with VERY large applications that use heavy stored procs.
As an experiment, lets consider the opposite view to see what value it might have. If we were to remove stored procs from the database entirely in any such large application, the code would become FAR more complex and far more difficult to modify. ANY database change would require searching thru large numbers of pages to make simple SQL statement changes. Only a developer would be able to make the changes and the code would need to be recompiled for every change. I've worked at companies where the lack of stored procs contributed to very messy and confusing code.
When executable code and database code are mixed together so tightly, it encourages a mixture of business logic and database logic.
From there it's only a small step to add in some GUI logic and we're right back in the days of spaghetti coding with a complete lack of separated layers.
A fundamental principle of coding is to use one or two word function names to execute more complex tasks. In the same way, stored procs are like functions that hide more complex tasks. Hiding complexity and dividing up complexity keeps coding simpler.
SQL is a separate language with it's own complexities that are best handled in it's own environment.
In summary, stored procs keep coding and debugging simpler by dividing the work up into layers.
Mark Tennenhouse

In response to Mark's comment about business/data separation, the correct recommendation for that problem is to separate your code into business classes and data access classes. That means that no business objects (whether persistent or not) should do any data access, and that no data access objects should implement any business logic. This guarantees business/data separation, and simplifies any subsequent modification. Any parameterized queries in your code are found in the data access layer implementation objects. There are many solutions for the lack of clear data/business separation in .NET, but although stored procedures are useful in many ways, they are not a good solution to that particular problem.

A step in the right direction:
<a href="http://www.ejbsig.de/docs/PetShopArchitecture.html">http://www.ejbsig.de/docs/PetShopArchitecture.html</a>;

The main problems I have seen in .NET web app design and dev in recent years is that ex-ASP and other such people are building n-tier object orientated apps with a relational perspective. They start by getting some loose set of functions that they think the client wants and call them 'requirements'. Then the next thing they do is do the data model and database (for chrissakes). This makes the whole application inherently 'relational', and when the OO programmers come to implement any business logic in C# or VB.NET the object-relational mapping exercise tends to be over complicated, and naturally a lot of logic and behaviour tends to fall into the database and outside of the code. For me, stored procedures in this kind of environment are a danger sign full stop, and one way of getting these people to turn their methodology around, and make the object model prior to the database model (and hence logic naturally falls into the object model and not the db) is simply to ban the use of stored procedures for anything other than report type queries and wotnot. Otherwise its a case of give 'em an inch and they take a mile. It starts of as one simple CRUD sp, and suddenly some db guru has converted into a t-sql neural network to detect market fraud.

Really the dynamic sql vs stored proc debate is good for at least one thing which is to open these people's eyes to the possibilities

What is your take on Microsoft Data Application Blocks?

The way I see it is that, if implementing something is going to require a whole pile of ad-hoc SQL statements to implement a single business operation then it's likely that it's better to implement it as a sproc. The network latency will kill you.

However, in that case, you maybe should look at the database design as well. Sometimes that's the reason why it takes a pile of statements to do something.

In my case it's trees and I work with people whom I shudder at the thought of explaining the nested set model for trees.

stored procedures are not bad. saying they're bad is like saying that guns are bad. stored procedures don't create bad code, people create bad code. I'm fine with you not using them. but please stop spreading rumors about how "almost all stored procedures are bad"

In in-house enterprise applications where one has complete control over the app and database, I "generally" opt for stored procedures.

In commercial retail boxed solutions which need to sit on any type of DB, I "generally" opt for parameterized queries using ANSI SQL. These parameterized queries can be further abstracted out into satellite assemblies, or other type of file (assuming some type of protection/security is implemented), so they can be replaced without recompiling the application, or messing with the database. When it comes to testing the SQL I usually hope for a DB programmer/DBA to give the SQL their stamp of approval, ensuring that even the parameterized queries are scripted uniformly.

Even if the commercial solution is tightly bound to SQL Server as its DB platform, I shy away from SP's because of prying eyes. I could solve that by compiling the sp using "WITH ENCRYPTION", but you better not loose those scripts!

Of course, these are generalizations, and I have to evaluate the options on a case-by-case basis.

This is interesting. Many good arguments made. For us, we try to exercise some restraint when it comes to sp's. Our ideal is to use parameterized SQL in a data access layer.

First, let me say that there are certainly reasons to use ad-hoc SQL in your applications. They provide a lot of runtime flexibility and in my mind are well suited to workgroup scale applications. In my view however, the story begins to change quite a bit when you move to an enterprise scale application or an application that relies on a strong security model.

If you look at a COM or other component interface, one of the primary values it provides is that the underlying logic can change but as long as we honor the interface contract, the caller doesn’t have to be aware of the changes.

This is a fundamental truth with stored procedures as well. Say you have a client application that issues a SQL Call. You’ve deployed it to 80 desktops. It worked fine for your first 40 users but as more users came online, it resulted in performance metrics that were problematic. You may get lucky and be able to solve the problem by adding an index, but what if the required change is really in the query itself? Maybe you are creating unanticipated locks and the query needs a NOLOCK hint. Then you have to deploy that change to the desktops. If the call were instead to a stored proc, you could optimize the SQL in that procedure and your client apps don’t know about it.

I work with a lot of .NET developers who know enough SQL to be very effective when building a custom application that serves a smaller audience. Most would fall down writing for big operations.

I’d also say that if you work on large development teams, the database layer is a natural dividing point for the work and allows the database interface to be reused by multiple clients effectively. Sure, you could code up the same select statement into the ten applications that the database serves, but this strikes me as a poor reuse model as well as an opportunity to introduce defects 10 times as opposed to once.

From a security perspective, I’d have to say that stored procedures in combination with views are almost certainly a more robust solution. As noted by another comment above, allowing ad-hoc SQL implies that the calling user context (whether integrated or standard security model is used) has access to the tables themselves. Such a model puts all the security control in the hands of the client when it probably ought to live in multiple layers. (Obviously, not all applications need to be particularly secure.) Restricting a user to the stored procedure interfaces not only abstracts the data, it protects the tables from a malicious user. If you want to then allow Ad-hoc queries, you expose the data you want exposed in a view, leaving sensitive data in the tables where the client can’t get it.

Re: "As an experiment, lets consider the opposite view to see what value it might have. If we were to remove stored procs from the database entirely in any such large application, the code would become FAR more complex and far more difficult to modify. ANY database change would require searching thru large numbers of pages to make simple SQL statement changes. Only a developer would be able to make the changes and the code would need to be recompiled for every change. "

As a further experiment, let's look at a well-designed app that uses parameterized queries. Tossing out an example of some app that uses SQL coded directly into web pages is a straw man. Any decent app has not only confined SQL to the DAL (as others have mentioned), but organized the SQL into classes for ease of maintenance. For example, in our application, you add a field to the database and add it to the associated business object. That's it. The DAL knows how to load, save, and delete. The business object knows how to enforce business rules. And recompiling isn't a bad thing. You can catch a lot of errors that way! Hacking up a stored proc in a live database looks likes a recipe for disaster to me!

Re: "Egads, I wasn't talking about having actual logins for the users of an app. I was referring to the logins that web apps run under."

If all users run under the same database login (which is usual, in my experience) then you've lost whatever fine-grained control you may have gained by using SPs. Also, who in their right mind is even giving users access to the DB? You can't touch our DB. You can only run the application. You might get a read-only database login with access to a few tables/views for ad-hoc reporting ... if you ask real nice. ;)

Re: "Say you have a client application that issues a SQL Call. You’ve deployed it to 80 desktops. It worked fine for your first 40 users but as more users came online, it resulted in performance metrics that were problematic. You may get lucky and be able to solve the problem by adding an index, but what if the required change is really in the query itself? Maybe you are creating unanticipated locks and the query needs a NOLOCK hint. Then you have to deploy that change to the desktops. If the call were instead to a stored proc, you could optimize the SQL in that procedure and your client apps don’t know about it.

Sure, you could code up the same select statement into the ten applications that the database serves, but this strikes me as a poor reuse model as well as an opportunity to introduce defects 10 times as opposed to once."

Hmmm, I'm not buying the "do it to ease deployment issues" arguement. If you have 80 desktops, I hope you've already got a deployment methodology in place. I'm also tempted to throw in a snide remark about lack of load testing, but I do realize that bugs happen in the real world. However, I'm always cautious when people want to fix them in 5 minutes. How many "5 minute fixes" have turned into all-nighters when you broke more than you fixed?

As for 10 apps on one database, if they're sharing the data they're probably sharing some business logic as well. IMO, code-reuse goes in the application. You *should* be sharing business objects and a DAL, so you *should* still only have one change to make. Now versioning those changes among the 10 apps is another animal - one that SPs doesn't solve either.

I would agree with others here that this is a great conversation. It is a discussion that I have had with numerous folks for over a decade. I will openly admit that I am a defector from the ad-hoc camp and have gone to the stored procedural dark side. There are numerous reasons and frankly they all stem from actual experience.

First, I would like to address the two point raised in your post. Intially I would address your comment “If your primary goal is abstraction, stored procedures are a terrible place to do that”. I simply cannot see any valid backing to that statement. You arguments correctly point out the danger of coupling but not abstraction. Consider abstraction where a framework is capable of being deployed upon say SQL Server or Oracle. Using commonly available objects in the .NET Framework we have demonstrated that the entire business layer can be 100% independent of any direct knowledge of the underlying database platform. That is a very critical form of abstraction. We can write a BL that can be deployed on Oracle or SQL Server without limiting the use of optimized SQL for each platform. That is a huge benefit.

Also, in your statement you mentioned that if the underlying data changes you have to change the stored procedure. Well, embedding ad hoc SQL in an application means the same plus you will have to recompile, build and deploy your application to address the change. The same is not necessarily true of the stored procedure approach precisely because it *can* help you decouple.

Also, I think there is an 800lb gorilla in the room with respect to this argument that nobody wants to discuss. Good developers don’t necessarily write good SQL. In fact, based on over a decade of experience with numbers major consulting firms, those that excel in writing OOP frequently suffer when trying to deal with how database platforms operate. By allowing the use of ad hoc SQL you distribute the SQL to many locations (within the code base) rather than leaving it in the hands of well trained database specialists. Further, the database specialist is limited about what they can do. This is why I have yet to ever encounter a DBA who prefers to have application developers gain direct access to tables.

Another point is that stored procedures simply are more secure and there is really no valid argument against this. Disallowing direct table access ensures that correctly written stored procedures can validate correct visibility to underlying data. Opening the database and exposing the tables lowers your security posture.
Lastly, there is no hope of SQL being generated and emitted from one machine can rival the performance of a well written stored procedure. I have gone through several benchmarking efforts and dynamic SQL never once bested the stored procedure approach. While the difference were rarely large differences the dynamic SQL approach never once came out on top.

Still, I love the debate. I think it will never end.

In many of these discussions, nobody seems to mention the application scope or application environment.
To me, the big difference in the stored procedure pro's and con's (and an addition to the 'it depends' answer) lies in the application scope and it's environment.

If your scope is to support ONE DBMS, ONE customer with ONE application and ONE version, your approach could be one.
If your scope is to support SEVERAL DBMSs, SEVERAL customers with SEVERAL applications and highly versionized, your approach could be quite another. (not to talk about numerious combinations of the latter or the addition of different hosting/deployment environments).

If supporting several DBMSs, your SPs would have to be duplicated to hold each DBMS's syntax. List your own consequences here.
If there exists business logic both in your code (.net, java, php or you-name-it) AND in your SPs, you have business logic in more that one place. List your own consequences here.
If there exists NO business logic in your SPs and instead they are only small clean 'DAL-doors', then their existence are questionable in the first place, why even have them?

Better performance is not only a database issue. A different modeling, normalization or programming approach alone could improve performance.

There are several answers to solving the SQL injection issue. Google it.

Code is for logic. Database is for saving.

Personal opinion: Because solutions should try to be future welcoming, foreseeing and able to solve yet-unknown-issues, then EVEN if my scope is ONE, ONE, ONE, ONE (see above), I still goes the 'without stored procedure' path.

RE: As a further experiment, let's look at a well-designed app that uses parameterized queries. Tossing out an example of some app that uses SQL coded directly into web pages is a straw man. Any decent app has not only confined SQL to the DAL (as others have mentioned), but organized the SQL into classes for ease of maintenance.

No, it's not a strawman at all. Whether you are embedding the code in an actual web page or in the code in a DAL the argument still holds true, that it's much more difficult to maintain.

RE: For example, in our application, you add a field to the database and add it to the associated business object. That's it.

No, that's not it...

RE: The DAL knows how to load, save, and delete. The business object knows how to enforce business rules.

Only after you tell them how with relation to the new column.

RE: And recompiling isn't a bad thing. You can catch a lot of errors that way! Hacking up a stored proc in a live database looks likes a recipe for disaster to me!

Talk about strawmen! You're assuming that the SQL developer is "hacking up" a stored proc in a live database. The stored procedure would of course be tested before being put in a production environment, but it can be done without a major deployment.

RE: If all users run under the same database login (which is usual, in my experience) then you've lost whatever fine-grained control you may have gained by using SPs.

Not at all. You can easily build in user-specific security in a stored procedure and it doesn't have to be tied to the actual login being used.

RE: Also, who in their right mind is even giving users access to the DB?

Actually, he SPECIFICALLY said that he wasn't talking about individual logins. Also, security isn't always about users. Do you think that developers or other internal personnel are never an issue when it comes to security? I can't count the number of times that I've seen a config file out on the web server with a SQL username and password sitting there in plain text for anyone with access to see. If security is tied to a Windows login then any developer with access to that login can log directly into the SQL Server and delete all of the rows of an entire table. "Whoops! I was just trying to fix a record!"

RE: Hmmm, I'm not buying the "do it to ease deployment issues" arguement.

Just because you don't buy it doesn't mean it's not true.

RE: If you have 80 desktops, I hope you've already got a deployment methodology in place.

Sure, and how many of those methodologies require that users reboot their machines or exit the application or wait for an overnight (or worse, weekend) release? How many require a reboot of a web server or similar?

RE: However, I'm always cautious when people want to fix them in 5 minutes. How many "5 minute fixes" have turned into all-nighters when you broke more than you fixed?

Yet another strawman (from someone who actually complained about strawmen arguments!). He didn't say anything about trying to do a 5-minute fix.

If you're selling software to other companies, you're going to have to support more than one DBMS and leaning on stored procedures is absolutely a bad idea -- no argument from me, there.

And yes, business logic in both the stored procedure and the higher-level code is going to be confusing. Don't forget, though, that keeping business logic entirely in the database is also an option. If you're careful with your database design, you can actually set up a pretty clean MVC separation with the database functioning as the model. It won't scale as far as a app-server system, but it's an awful lot simpler and should be good for a few hundred users.

One more thing about stored procedures. It would be nice if there was a stored procedure that could, with one shot, check the syntax of each and every stored procedure inside a database. The syntax should check for field validity. If any fieldname is changed in the scenario of thousands of stored procedure, not only are SP's going to break, but automated maintainance processes that copy databases to backups will also fail because SQL verifies that all SP's are correct before executing a DB copy or backup.

If the database schema changes then using an API is not really going to give you abstraction you still need to modify your API to take into count the new schema changes.

I totally agree with "Mark Tennenhouse on October 11, 2005 07:55 PM"

Divide and conquer. Let the technology do what it was designed to do. Mixing code platforms only creates more unnecessary complexity...

"K.I.S.S."
"Just because you can, doesn't mean you should"
"Who wrote this crap!?"

:-)

Obviously, I think we are missing the points of both stored procedures and raw SQL. Both have their own advantages.

As previously said raw SQL is a great solution for parameterized reporting and more complicated DB tasks(db backup, restores, table creation, database definition, etc.) Really, I would say anything which can not be automated easily, should be written in raw SQL. Since automation is difficult, more than likely so will control. This usually is a sign of inconsistency at one or more levels in the database, whether security, data, structure, or flow of control.

Stored procedures were created to place limitations on code/programs. Limits such as the number of recompiles, limitations on the executing code(if designed correctly you always know what an stored procedure is going to do, no exceptions), limits on the applied database security(keyword is applied not fundamental),and limits on the usage and redundancy(more consistency in your coding).

All in all the correct usage of these fall in the hand of us as IT Professionals. We must first know the advantages/reasons for which processes, such as stored procedures, were invented to control. Then, naturally the power to use each in the way intended is going to be a decision which ultimately falls into the hands of not the code or stored procedure, but into the hands of us as IT professionals.

"The aim of an argument or discussion should not be victory, but progress."
- Joseph Joubert

G-CODE

Here's a good reason to use stored procs when using VB (vs. C#):

VB's god-awful string concatenation versus C#'s @'string' construct. VB makes me choose between nicely formatted sql code or LOTS of " & _

People talked about CRUD sps. Anti-SPers cite how boring and mindless they are to write. Pro-SPers counter with how you can use code generators to create them. However, one of the purposes, major purpose IMO, of SP is to hide database schema from application logic. So if SPs are used correctly, there should not be so many CRUD SPs at all, let alone 400+ or 600+ CRUD SPs for a 100 table database.