I know next to nothing about SQL Server

I've never used SQL Server. My comments refer to Oracle.

Oracle lets everyone create and manage his own stored procedures.

Only if you have DBAs who are happy to grant that privilege. None of the ones I've worked with were.

When you back up your database, that backup includes stored procedures. You can update them very easily using the DB SQL client. Of course, you want to put them into source control. You can use the same source control system you use for your Perl code.

No special procedure is required to make perl code live in a typical environment. For stored procs, you have to connect to the database and install the new version. It's not horrible, but it is more work than file-based stuff. And it adds to versioning problems between code and database. A good automated code deploy tool could probably fix this issue, but most places don't have one.

There are also the performance advantages of not having to parse and prepare the SQL every time (that will be done only once, and not just once per connection, which is the best you can do from the outside), of using native database datatypes, and of reduced network traffic.

With Oracle, when you use placeholders correctly, statements are parsed and then kept in the cache. Sending the request again doesn't require it be parsed again. The network stuff is a savings if you need to examine lots of rows but won't actually be needing them in your final results, as I mentioned. Otherwise, it's negligible.

accessibilty of the code from other programming environments

That's the big argument in favor of stored procs, and the only one that I actually buy. I don't think PL/SQL is an adequate language for this kind of development though.

Forgive the rant

Ditto! We disagree about most of this, but some of that is surely due to different database policies at our places of work.

try to keep my programs completely free of any SQL (for the same reasons that I keep them free of HTML)

There are some nice modules for keeping your SQL statements in a separate file. That's a nice way to do it for people like me who want to avoid stored procs.

In reply to Re: No stored procedure bashing on my watch! by perrin
in thread Recoding a multi-sql-statement storedProc transaction in a script by punkish

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.