I would always recommend stored procedures, as long as there are a
very few rules obeyed:
- keep them simple - they should mostly implement just CRUD operations
plus application-specific searches, and should not encapsulate any
other logic
- use just portable SQL (well, as long as this is possible)

My reasoning for using stored procedures and sticking to these rules
is the following:
- no matter what you do, especially with PHP, you can't achieve the
same performance if you generate your SQL on the fly as when you just
call a precompiled stored procedure
- by keeping stored procedures very simple, and sticking to the
convention of packing just CRUD + specialized searches into them, plus
using just portable SQL, inasmuch as possible, you can easily switch
databases - in most cases, just copying over the stored procedures
does the trick
- for the same reasons listed for the previous point, the readability
of your application is much improved - reading
"users_getByLogin(:login)" is IMO easier to comprehend than "SELECT *
FROM USERS WHERE loginName = :login", without sacrificing any
performance or portability, compared to using inline SQL statements as

The consequences of not sticking to the above listed two criteria can
be very bad:
- packing more than reasonable logic into the database makes your
application incomprehensible - for instance
"company_doMonthEndCalculations()" is likely to include such a huge
quantity of logic, that the PHP code calling it is mostly irrelevant,
and you must actually comprehend both the details of the SQL in the
database (in the stored procedures) and the way PHP is connecting them
to understand an application - obviously harder if you have all your
logic in just one place
- using non-portable SQL may be quite a deterrent from porting to a
new database, or may cause a lot more effort than needed, and isn't in
fact justified in most cases
- whereas if packing only very specific and simple operations into
stored procedures allows you to keep the design of the PHP application
very object-oriented, packing very much logic into stored procedures
may cause your PHP code to be just an adapter to an application
written in SQL, instead of being the application itself; SQL being
procedural, your application will have all the flexibility,
extensibility and maintainability problems that a non-OO design causes



Fine counsel is confusing, but example is always clear. (Edgar A.
Guest, The Light of Faith)

PS: I'm not trying to be a smart-ass, but IMO stored procedures are
underrated (not just by PHP programmers), and it's a pity (and it
leads to sub-optimal applications, and to the development of cures for
the symptoms instead of the cause, at least on other platforms than
PHP), and not letting a database do what it does best is simply
stupid. That's why I try advertising their use whenever I have an

PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to