The fundamental reason for using stored procedures is performance.
Stored procedures are compiled code. This means the database has reviewed the SQL, came up
with the most efficient plan of action (often sorting through thousands of permutations
when multiple table joins are concerned), and (given the right directives from the DBA)
has this plan of action cached in memory, waiting to be called by name (optionally with
When you issue your SQL directly from the application or business tier rather than use a
stored procedure - you are issuing what's called dynamic SQL. The process of coming up
with a plan of action for this SQL repeats itself every time the SQL is issued and is
In sophisticated transaction system it's common to see a 1000-fold improvement in the
number of transactions per second processed - when switching from dynamic SQL to stored
procedures. This is a big deal.
On a small application with simple SQL and not much action - it's less of an issue. On an
airline reservation or amazon type situation - stored procedures are an absolute must,
and yes, they tie you to the specific database at hand because every database's stored
procedure language is different (that's one way for the vendors to make it hard to
switch). No pain no gain.