An interesting thread on using stored procedures over on Slashdot got me thinking about the importance of stored procedures.

The comments seem to be grouped into two schools of thought: 1) Create the logic in the application or 2) Create the logic in the database. I think it comes down to where the abstraction is needed.

Database Abstraction

For example, if you application has database abstraction (that is it can work with a variety of RDBMS), then the logic has to be stored in the application. Many of our application fall under this category.

For the actual database calls, many times we use DB abstraction modules (PEAR::DB for PHP and DBI for Perl). To make the application truly portable, we actually have abstracted the SQL along with the actual calls in XML files across the database flavors we would support.

Here is a simple example returning the last ID abstracted for MySQL and MS SQL Server:

<?xml version="1.0"?>
<PROCEDURES module="core">
<SQL id="SELECT-lastid">
   <DESCRIPTION>Return the last insert id</DESCRIPTION>
   <STMT type="mssql">SELECT @@IDENTITY AS last_id</STMT>
   <STMT type="mysql">SELECT LAST_INSERT_ID() AS last_id</STMT>
</SQL>
</PRODEDURES>

Now based on the database type (which we read in the application configuration), we know which SQL statement to issue.

You may be questioning the overhead of reading XML files every cycle, and rightfully so. We typically use a caching module to read in the XML and convert into a native array-hash (PHP, Perl, etc.).

Application Abstraction

One of the main arguments the stored procedure crowd has (forgetting about performance for a minute, which can be a factor), is that other applications written in different languages could need to run the same SQL-business logic by calling the same stored procedures.

This is true, but it could be satisfied by extend the original application with a standard API like CORBA (in the old days) or Web Services (SOAP or XML-RPC).

In my experience I find it difficult to describe entire business logic as stored procedures, so some sort of API-duct tape is usually required. Therefore, extending the pieces of that could be fully described in the same API is not that far a stretch.

Summary

SPs are a great and elegant way to create a tight application if you have decisive control of the database (or at minimum the database requirements). But the moment a SP cannot be migrated from database X to Y because of lacking functions/features, you will most likely have to abstract the SQL logic in the application.