DEV Community


Discussion on: When is it a good idea to use stored procedures?

moopet profile image
Ben Sinclair

I like them, and I use them for things like working out latitudes and longitudes and great circle distances quickly. If you have more than one service which accesses the database and would be performing the same simple logic on those data then why not keep it in one place? Your database is initially generated from code, be it through fixtures, migrations or just a big old constructor. It's not like it's not going to be in your VCS.

On the other hand, I know they're not necessarily ideal in production depending on your situation. You have to be careful with stored procedures and triggers because they require separate permissions and your database host might not allow them.

For example, Acquia disallow them completely. They're the big Drupal host and contributor, but if you want to use packages which rely on triggers like CiviCRM with them you'll need to host the database elsewhere. That adds another layer of complexity to your infrastructure.