Prepared Statements vs Stored Procedures

Prepared Statements

  • Compiled and cached by the database (assuming you use them properly and bind your parameters).

  • Are compliant with the MVC paradigm as no logic is in the database.

  • Don’t tie you in to a particular database vendor (assuming you write portable SQL).

    Stored Procedures

  • Compiled and cached by the database.

  • Break MVC as they move some of your application logic into the database.

  • Do tie you in to a particular database vendor (this in my opinion is the real reason Oracle etc tell you to use them).

    Clearly, I am a fan of Prepared Statements and I don’t like Stored Procedures.

    The only people I know who rave about Stored Procedures are clueless consultants and people selling databases.

    So, given that the above is all the knowledge I have on them, I want to know if there actually are reasons I should use them.

    Or to put it another way, am I missing something?

  • 1 thought on “Prepared Statements vs Stored Procedures”

    1. The answer, as always, is "it depends". In a loosely coupled architecture you can put some of the logic in the database because then access to your data is consistent regardless of where you are coming from. The classic example of this is plugging a reporting tool into your application database (and I know reporting should be in a different database, but this is the real world).

      The only other practical use is for noddy "on the fly" calculations like turning date of birth into age or various other unit of measure conversions.

    Leave a Reply

    Your email address will not be published. Required fields are marked *