This article originally appeared in REALbasic Developer Magazine 4.2 (Nov/Dec 2005)

If you find yourself working with database servers, you may be wondering about stored procedures. Depending on who you talk to, stored procedures are either something you should definitely be using or something you should avoid like the plague. As with most things in life, the answer is not quite so simple.

What is a Stored Procedure?

A stored procedure (SP) is code that runs on the database server. Generally this code is written in a language that is proprietary to the database server you are working with. Some database servers may support more than one store procedure language. Figure 1 is a table of some common database servers and their corresponding stored procedure languages.

I’ll be focusing on the SQL-type programming languages as they are much more commonly used.

Code Listing 1 shows you how a very simple stored procedure looks with PostgreSQL. This is a very simple example that justs inserts a row of data into a log table. The syntax across the various databases is similar, but different enough to be annoying when you work with more than one. They become much less similar when you start working with variable declarations, datatypes and control structures.

As their name implies, stored procedures are procedure-oriented, not object-oriented. They’re not called stored objects, after all. This means there is no support for things like inheritance. Oracle has a little support for data encapsulation with its Stored Packages, but we won’t go into those now. Also note that if you did choose to work with other stored procedure languages, you would get to use object-oriented features, but as I mention above these are not widely used.

Why Should You Use Them?

Stored procedures use a language you probably don’t know and are usually not object-oriented, so why would you consider using them? Well, stored procedures can be quite fast and therefore are frequently used to improve the performance of a database application. Since the code lives right alongside the database, you eliminate almost all network traffic and can allow the database server to work to its fullest.

Another benefit of stored procedures is that they can provide a nice way to encapsulate business logic near the data it is modifying. This can be useful if you expect to have several ways to access data (perhaps both a desktop and web application) and want to ensure a consistent API. It is easier and less error-prone to update this business logic once on the server rather than by updating multiple applications.

Lastly, if you’ve paid a lot of money for expensive database server software, it seems silly to not use all the features it can provide.

Why Should You Avoid Them?

The primary reason to not use stored procedures is that they are not portable. Using stored procedures effectively locks you into using a single database vendor. If you need your application to run on a different vendor’s server, then you’ll need to recreate all your stored procedures. This can be a huge amount of work or can be completely impractical depending on the database servers you use.

Wrapping Up

I’ve worked on projects and products with hundreds of stored procedures, just a few stored procedures, and no stored procedures. The rule I generally follow now is to not rely on stored procedures during initial design and development. I design my applications so that they could work with any database backend. As the product is nearing completion, I review areas that may need performance improvements to see if a stored procedure would make sense . If so, I’ll add the ability to use a stored procedure while at the same time retaining the ability to work without the stored procedure.

Next time we’ll look at more examples of stored procedures using syntax for several different database servers. We’ll also look at how stored procedures can be used with REALbasic.

Simple PostgreSQL PL/pgSQL Stored Procedure

CREATE PROCEDURE INSERT_TEST AS
BEGIN
INSERT INTO testlog (name, connectTime, status)
VALUES ('Bob Roberts', now(), 'Active');

COMMIT;

END;

Figure 1: Databases and Stored Procedure Languages