The Great Stored Procedure Debate

Really, its all been discussed to death already

Stored Procedures vs Do it in Code

Benefits:

• All the Code is in one place
• One Programming Language
• No code-to-database bottleneck
• ???

Problems

• Harder to interface with code
• Lower reusability
• HUGE maintenance problem

Benefits:

• Reusability
• Tools
• Testing
• Code Clarity
• Less development time
• People cost
• More organized code
• Has superseded/supplanted procedural database programming almost 15 years ago

There is a time and a place for Stored Procedures, but they should not be the default position.

OOP: Libraries and Reusability

Procedures

• functions
• variables
• shuffle data from one function to another

Objects

• Customers, Tanks, Products, Contracts, Etc.
• Far more like working with the domain, more like building a business-specific language than shuffling data

And in a real language, it's far easier to build a Domain Object library

EDC2 Objects

EDC Squared

Driver Interface

Contracts Updater

Maintenance - Clarity

A Naive Example:

We want to get a tank, if it doesn't have a capacity change it to 100,000lbs

Procedural Style:

ds  = DAL.GetDataSet("pkg.GetTankInfo", 100);

if((ds["capacity"] == DBNull.Value) && (ds["uom_id"] == DBNull.Value))

    DAL.StoreData("pkg.SaveTank", ds["tank_id"], ds["name"], 100000, 7);

Object-Orientation Style:

tank = TankDao.GetById(100);

if(tank.Capacity == null)

    tank.Capacity=new Quantity() {Amount=100000, Uom=Uom.Pounds};

tank.Save();

Which is clearer? The same Tank code can be used everywhere!  No need to remember anything else.  Intellisense in the IDE can help.

Maintenance - Testing

Automated Testing is far easier when programming properly with OOP

Automated Testing

• Decrease errors at time of release
• Makes code far easier to read and understand
• Makes code easier to change without breaking
• Save time in the medium and long run

http://www.wsdmag.com/Articles/ArticleID/8337/8337.html

http://www.ddj.com/development-tools/206105233

http://www.amazon.com/gp/product/0131177052

+ Millions of others

Maintenance - Tools Available

Programming Languages Have:

• IDEs (i.e. Visual Studios)
• Debuggers
• Testing Frameworks
• Dependency Injection Frameworks
• Database Mappers
• Test Coverage Tools
• Template Engines
• Refactoring Tools

PL/SQL has:

• Toad (doesn't even have search/replace)

Maintenance - Personnel

PL/SQL programmers are far rarer

• Dice results for .NET: 9304
• PL/SQL: 3755

Writing Stored Procedure Programs

Is Not a DBA Job

• Typically DBAs hate this
• Typically DBAs are bad at programming
• Dice results for Oracle Programmer: 1218

Why DO People Use Stored Procedures?

• Speed - Stored Procedures are Faster
• Security - Authorization
• Security - SQL Injection
• Ease of Deployment

Are any of these real issues?  Do any of these apply to us?

Why DO People Use Stored Procedures?

Speed

• Old myth, barely if at all true
• If database access is a bottleneck then by all means use them. 
• NOT the case for us.

Security - Authorization

• We only have one user with all permissions enabled!

Security - SQL Injection

• Parameterized SQL / Dynamic SQL
• In an attempt to make PL/SQL code reusable we sometimes undo this benefit altogether.

Ease of Deployment

• Only faster if you don't have a proper deployment process
• Source Control?  Version History?  Synchronization horrors!

More: http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx