I don't think there is anything wrong with using stored procs over inline
SQL to retrieve data. However, in my view of the world this is application
behavior and not reporting. Application behavior should be modeled in
objects and the best way is to use persistent components (Hibernate/ORM.)
The code would then look like
claim = entityload("claim", {claimid=1234});
documents = claim.getDocuments(); // this uses ORM to generate SQL that
really just does select * from documents where claimid=1234
document = documents[1];
formfields = document.getFormFields();
IMHO, there is no value of inline SQL over stored procs other than perhaps
that people seem to do a poor job of version control on stored procs. An
additional benefit of a stored proc is that database guys will go in and
review and tune a stored proc, but are unlikely to go and edit inline SQL
as that falls in the application space. I would say leave your stored proc
alone unless you are going to go all the way to ORM.
But... that is just my view of the world.
Jason
On Tue, May 21, 2013 at 9:43 AM, MCG <mger...@gmail.com> wrote:
> Yes it's been a while, but I've gotten pulled into database design, and
> this group has always been the best place to turn to for technical
> knowledge.
>
> The team here inherited a crap database, over 200+ tables for an
> unexciting claim system, and is now adding new features. Yes, views are
> better than straight SQL in the code in the case of many tables for your
> query. Maybe I've just been out of the game, but the team is using stored
> procedures to build queries to output data on a page, which seems odd to
> me. Maybe it is fear of the outer join, unions, subqueries.
>
> But do I have a case for pushing the funky view SQL versus running stored
> procedures? The case here is nothing exceptional, we are getting data for
> the list of all documents related to a claim and then the one record for
> form fields for a single document for a claim.
>
> Thanks!
>
> Mary-Catherine
>
