My experience is with Sybase, and that may color what follows...

Stored procs are great, and can be used to achieve a number of objectives. The first is speed - the proc is pre-compiled and pre-optimized, and the optimization of a complex multi-table join can take a significant amount of time. The second is access control - you give access to the proc, not to the underlying table, and so you control what DML a client app can execute. The third is modularity - you can hide some DDL changes within the proc architecture without having to affect the client apps (this is of course not a complete solution, but for some things it can work pretty well.)

I haven't used views as much, but they can achieve similar results.

I would advise against having dynamic SQL all over the place in your client code - this makes maintaining the database and the dependancies in the client code a nightmare. In addition the dynamic SQL is really quite difficult to optimize when you hit a bug in the DB where an optimization hint might be needed (such as index hints, abstract query plans, etc.). My experience here has been as a DBA for a team that develops with WebObjects, and the results are sometimes quite ugly.

This is of course the view from the DBA, not so much the SQL/app developer, but may still be of use to you.

Michael

In reply to Re: [Semi-OT] Views, Stored Procedures, and Class::DBI by mpeppler
in thread [Semi-OT] Views, Stored Procedures, and Class::DBI by jgallagher

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.