6 ms·

For one, stored procedures are hard to test, debug, maintain and check into source control. But don't let that get in your way of ignorantly generalising abou

• bni 11y ago
  "hard to test, debug, maintain and check into source control." Why? I have never had problem with any of these. SPs is just imperative code like any other imperative code.
  • adamors 11y ago
    Well, you cannot isolate the code, you cannot unit test it, you cannot use a debugger, cannot set breakpoint, have stack traces etc. You are tied to the database at all times. In my experience, every stored procedure that is larger than 2-3 lines is a headache.
    • bni 11y ago
      All of those are no problem with the right tools. Any database IDE can debug SPs. You can unit test SPs like any other code just use a testrunner.
      • glogla 11y ago
        Yeah. I suspect that lot of hate of logic in DB is because of bad Oracle setups many years ago, kind of like lot of people think SQL is useless because MySQL is.
    • arethuza 11y ago
      I'm not a huge fan of stored procedures - but I'm pretty sure you can debug stored procedures in SQL Server pretty easily from Visual Studio - I think you can "step into" the call to SPs while debugging client code.
• glogla 11y ago
  Stored procedures may be all of those things, but they don't have to - it's just that most of the time, developers don't really care, so they have fancy versioning, deployment and continuous integration for all their code, except for stored procedures. Here is interesting talk about database migrations and stored procedures and unit tests: http://www.pgcon.org/2013/schedule/events/615.en.html http://www.pgcon.org/2013/schedule/events/615.en.html Also, DB procedures are not easy to "debug" in the traditional way, but SQL client is basically the first REPL every programmer becomes familiar with. You can easily step stored procedure by running it's commands one by one, unless it's fancy Oracle forall loop with cursor or something (and the cursor select can still be selected as normal). Also, databases tend to have more strong data types than programming languages in general so putting constraints in DB means, that bad data are not savable in the system.
  • batou 11y ago
    Hmm no. Avoid state at all costs. Stored procedures are stateful. Schema and migrations is pain enough already. Write me a check constraint that validates an email address being put in a varchar column and reports back a sensible message which can be bound to an entry field with metadata about the error. Write me a constraint and key arrangement which is unique across two and three columns in separate groups. No. You're wrong.
    • nunwuo 11y ago
      > Avoid state at all costs. Stored procedures are stateful. Schema and migrations is pain enough already. What do you mean by that? How is having a bunch of queries in a stored procedure more "stateful" than having the same queries in the application? > Write me a check constraint that validates an email address being put in a varchar column and reports back a sensible message which can be bound to an entry field with metadata about the error. Postgres gives you metadata about the error, though the error message will still be a generic "CHECK constraint violated" or some such. > Write me a constraint and key arrangement which is unique across two and three columns in separate groups. I'm not sure what you want to see based on that description, but surely you're not advocating enforcing unique constraints in the application?
• jasonlotito 11y ago
  I tell that to our DBAs who test, debug, maintain, and check into source control our SPs.