Some Observations
Stored procedures give you code reuse,
and encapsulation (two pillars of
software development),
Only if you use them correctly in the context in which they are supposed to be used. The same claim can be said about functions (in structured programming) or methods (in object oriented programming), and yet, we see 1K functions and mega-ass objects.
Artifacts don't give you those benefits. The proper usage of those artifacts is what give those benefits.
security (you can grant/revoke permissions on an individual stored proc),
Yes. This is a good point and one of the main reasons I like stored procedures. They provide a finer-granularity access control than what can be typically achieved with just views and user accounts.
protect you from SQL injection attacks,
That is not specific to SPs since you can achieve the same level of protection with parameterized SQL statements and input scrubbing. I would use SPs in addition to those, however, as matter of "security in depth".
and also help with speed (although
that DBA said that starting with SQL
Server 2008 that even regular SQL
queries are compiled if they are run
enough times).
This is highly database vendor specific, but in general your DBA is right. SQL statements (either static or parametrized) do get compiled. SPs help if you want/need to aggregate and compute data that you cannot do with simple SQL statements, but are tightly integrated with SQL and does not warrant the round-trip to the app server.
A good example is querying data into a temporary cursor (or cursors) from which to run another SQL itself. You can do it programmatically in the app server, or you can save the multiple round-trips by doing it in the db.
This should not be the norm, however. If you have many of those cases, then that is a sign of bad database design (or you are pulling data from not-so compatible database schemas across departments.)
We're developing a complex app using
Agile software development
methodology.
Agility has to do with software engineering processes and requirement managements, and not technologies.
Can anyone think of good reasons why
they wouldn't want to use stored
procs?
Wrong Question
The question is wrong and equivalent to asking "are there any good reasons not to use GOTO"? I side with Niklaus Wirth more than with Dijkstra on this subject. I can understand where Dijkstra's sentiment came from, but I do not believe it is 100% applicable in all cases. Same with store procs and any technology.
A tool is good when used well for its intended purpose, and when it is the best tool for the particular task. Using it otherwise is not an indication that the tool is wrong, but that the wielder doesn't know what he/she is doing.
The proper question is "what type of stored procedure usage patterns should be avoided." Or, "under what conditions should I (or should not) use stored procedures". Looking for reasons not to use a technology is simply putting the blame on the tool as opposed to placing the engineering responsibility squarely where it belongs - in the engineer.
In other words, it is a cop-out or a statement of ignorance.
My guess was that the DBAs didn't want
to maintain those stored procs, but
there seem to be way too many
negatives to justify such a design
decision.
What they are doing then is projecting the results of their bad engineering decisions on the tools they used poorly.
What to do in your case?
My experience is, when in Rome, do as the Romans do.
Don't fight it. If the people at your company want to label store procs as a bad practice, let them. Be advised however, that this can be a red flag in their engineering practices.
Typical labeling of things as bad practice is usually done in organizations with tons of incompetent programmers. By black-listing certain things, the organization tries to limit the damage inflicted internally by their own incompetence. I shit you not.
Generalizations are the mother of all screw ups. Saying that stored procs (or any type of technology) are a bad practice, that's a generalization. Generalizations are cop-outs for the incompetent. Engineers do not work with blatant generalizations. They do analysis on a case-by-case basis, do analysis trade-offs and execute engineering decisions and solutions according to the facts at hand, in the context in which they are supposed to solve a problem.
Good engineers do not label things as bad practice in such generalizing ways. They look at the problem, select the tool that are appropriate, make trade-offs. In other words, they do engineering.
My opinion on how not to use them
Don't put complex logic beyond data gathering (and perhaps some transformations) in them. It is ok to put some data massaging logic in them, or to aggregate the result of multiple queries with them. But that's about it. Anything beyond that would qualify as business logic which should reside somewhere else.
Don't use them as your sole mechanism of defense against SQL injection. You leave them there in case something bad makes it to them, but there should be a slew of defensive logic in front of them - client-side validation/scrubbing, server-side validation/scrubbing, possibly transformation into types that make sense in your domain model, and finally getting passed to parametrized statements (which could be parametrized SQL statements or parametrized stored procs.)
Don't make databases the only place containing your store procs. Your store procs should be treated just as you treat your C# or Java source code. That is, source control the textual definition of your store procs. People rant that store procs can't be source controlled - bullcrap, they just don't know what the bloody hell they are talking about.
My opinion in how/where to use them
Your application requires data that needs to be transposed or aggregated from multiple queries or views. You can offload that from the application into the db. Here you have to do a performance analysis since a) database engines are more efficient that app servers in doing these things, but b) app servers are (sometimes) easier to scale horizontally.
Fine grain access control. You do not want some idiot running cartesian joins in your db, but you cannot just forbid people from executing arbitrary SQL statements just like that either. A typical solution is to allow arbitrary SQL statements in development and UAT environments, while forbidding them in systest and production environments. Any statement that must make it to systest or production goes into a store procedure, code-reviewed by both developers and dbas.
Any valid need to run a SQL statement not in a store proc goes through a different username/account and connection pool (with the usage highly monitored and discouraged.)
- In systems like Oracle, you can get access to LDAP, or create symlinks to external databases (say calling a store proc on a business partner's db via vpn.) Easy way to do spaghetti code, but that's true for all programming paradigms, and sometimes you have specific business/environment requirements for which this is the only solution. Store procs help encapsulate that nastiness in one place alone, close to the data and without having to traverse to the app server.
Whether you run this on the db as a store proc or on your app server depends on the trade-off analysis that you, as an engineer, have to make. Both options have to be analyzed and justified with some type of analysis. Going one way or another by simply accusing the other alternative as "bad practice", that's just a lame engineering cop-out.
- In situations where you simply cannot scale up your app server (.ie. no budget for new hardware or cloud instances) but with plenty of capacity on the db back-end (this is more typical that many people care to admit), it pays to move business logic to store procs. Not pretty and can lead to anemic domain models... but then again... trade-off analysis, the thing most software hacks suck at.
Whether that becomes a permanent solution or not, that's specific to constrains observed at that particular moment.
Hope it helps.
