Stored Procedures

Procedures or algorithms which are made part of a RelationalDatabase schema, typically implemented in a database language (such as SQL or some flavor thereof), and executed as the result of some transaction -- either explicitly, or as the result of a DatabaseTrigger?. They are often used to implement database state change mechanisms that cannot be represented as a single declarative insert, update or delete, or cannot easily be represented as a simple combination of these. For example, in a database designed to support an employee scheduling system, a stored procedure might be used to implement the main "schedule" algorithm that assigns employees to time slots.

Unlike client code, stored procedures run "inside" the database; this has both advantages and disadvantages.

Pros:

They're fast because they're tied closely to the database.
They're the only generalized and reliable way to ensure that some action "X" takes place whenever a record is inserted/updated/deleted in table "Y".
PL/SQL (in Oracle) and possibly other are really pretty good procedural language with relational extensions. This makes them very handy to write some transactions as efficiently and elegantly as possible (fewer lines of code than it would be done in a host language).
Independent code control and release procedures from the main line of procedural code. Some times it is a lot easier to correct a problem in a stored procedure as opposed to creating and deploying a new software release for the field.
Application and DB have clear interfaces between them, just parameters and result set, if you consistently use StoredProcedures in all of your app code.
Portability in the unlike event of switching DB, you just need to reimplement stored procedures, the interfaces between caller and callee can largely stay the same.
Queries can be tuned independent of application code
Queries can be changed independent of application code, no recompilation of application code needed.
Easy to test.
Easier security control. You don't need to grant access to tables, views or synonyms, you grant access on stored procedures which encapsulate the low level database objects.
Easy to discover.

Cons:

Independent code control and release procedures from the main line of procedural code. Whenever you "make a release" you must carefully control both application code and the corresponding stored procedures. If you mess it up, or really during the period of time when it must be out of sync, the system breaks -- often in unpredictable ways.
- Changes to StoredProcedures typically must involve the DBA.
- However, many environments are significantly heterogeneous that even for "application" code, there are different development environments.
DatabaseTrigger?s cause side effects. These side effects may be "surprising" and "undesirable" in some cases -- like when you want to purge old data, copy data en masse between databases, or "rename" some business entity (in spite of your poor choice of primary keys).
Although some vendor products support a variety of procedure implementation languages -- including OO ones such as Java -- these are (as of May 2005) considered somewhat experimental. Therefore, the following is generally considered to be true:
- Stored procedure languages are generally procedural, lacking the advantages of the object oriented languages used outside the database.
- Stored procedure languages are incompatible with your host language, making refactoring difficult.(In particular, migrating logic from the application to the StoredProcedures, or vice versa, requires rewriting in a different language). This also leads to specialization by the programmers, causing forces on the project that often force bad compromises in the design. (Like "I'll just throw in this hack in the stored procedure because I can't or won't wait for or talk to the host language programmer." We've got a good and bad design here; we'll take the bad because we have more "language X" programmers available to work on it.)
- Stored procedure languages often impose arbitrary and poorly documented limitations on size of modules and call depth allowed while running.
- Stored procedure languages are often little better at "conveniently" processing relational data, but lack dynamically managed memory, structures, and other language features that would make complex processing feasible.
Portability you cannot easily transfer the database to an other environment or operating system
Unclear layering unless you put all of your business rules in SP, your business rules will be split in two layers, with no easy way to cross reference.
Different change velocities the application layer (in C/C++/C#/Java/Perl/etc) usually change faster than the database (during development and after production), if your business rules are spread between the application layer and the database, friction results.
- On the other hand, some "rules" are more stable than others. You wouldn't want a StoredProcedure for every ad-hoc query that some manager might want to make. OTOH, a query or transaction which is a key part of operations is a good candidate for a StoredProcedure--and those are less likely to change, without a correspondingly difficult change to operations.
It is hard to come up with good names for stored procedures (either they end up with meaningless names like FindCustomer? and 25 parameters (a GodStoredProcedure?) or they en up with funny names FindCustomerWithOverAverageRequestsTheLivesInCityAndHasAnAgeOfAndHasAlwayPayedOnTime?.
Most databases (all I know) do not allow you to have NameSpaces? or any other way to group related StoredProcedures (so, after a while it can be hard to find all the stored procedures that affect something.
It is particularly hard to represent with stored procedures a query on which you want to allow the user to sort by any column (or a group of columns) interactively (you end up having to reinvent Order By)
If you already have an stored procedure that returns a particularly large number of rows it is non easy to add pagination functionality to it (in fact AFAIK it is impossible to do it without direct modification, and then you have to maintaint two versions of the same query one with pagination and another without it (just compare that the very easy to use pagination API of a good ObjectRelationalMapper like Hibernate) )
Most databases don't allow you to pass complex data (object graphs or structs) as parameters for StoredProcedures so a lot of times you end with very long and hard to maintain parameter lists
Some databases don't allow you to make a query over the result of an stored procedure (select temp.* from (exec StoredProcName?(param1) as temp) so it becomes hard to reuse the logic you have on a stored procedure on different contexts
Stored Procedures give people a false sense of security: Now that I have forbidden the acccess to the MoneyAccounts? table everything is safe, now only the stored procedures "CreateAccount?","FindAccount?","DepositMoney?", "TransferMoney?", "CloseAccount?" and "DeleteAccount?" can be called (it should be easy to see, that if someone is able to call those stored procedures he/she will be able to do whatever he/she wants without having to see the table directly).
Hard business related error reporting: It not easy to send, from inside an stored procedure a message like You cannot transfer money to the account {X} because the account {Y} only has ${X} and you want to transfer ${Z}

Some say that StoredProceduresAreEvil.

Discussion regarding the evilness of StoredProcedures moved to StoredProceduresAreEvil.