business process logic in stored procedures

microsoft.public.sqlserver.programming

6 Apr 2006 11:52 AM

Will

Hi All,

I've always tried to think of and use sql as being a tool to quickly
retrieve / update relevant data, and let other parts of a system handle
the decisions as to what needs to be done to the data. However I keep
coming across (and sometimes find myself creating) the situation where
there are massive stored procedures which will have several different
statements, pulling data from loads of tables and updating others based
on some business logic.

First question: is this a bad thing?

as I see it this has the advantage that updates can be expressed as a
function to be applied over a whole table, making the process much
faster than if the data was changed in a business layer, then
propogated to the database. However the "code" is hard for someone else
to understand, and hard to re-use/improve. I often find that processes
which would be modelled with quite a large framework of objects are
condensed down into a large stored procedure, such that to anyone else
looking at it will just see a mass of update insert and selects with no
idea why.

Second question: when to do it?

I have found that at times it's unaviodable, either for performance or
simply the ease of access to all the data that I need to use a stored
proc, does anyone have any rules of thumb as to when it's a good/bad
idea?

Third question: what are the alternatives?

I'd hope that there are other ways to get around the problems that
people are solving using sql, does anyone have any links/suggestions of
how to approach things that people would often resort to sql for, using
more maintainable methods?

I could probably rattle on for days on this issue, but I'm hoping maybe
people will be able to suggest some best practice about this.

Cheers
Will

6 Apr 2006 1:08 PM

Uri Dimant

Will
1),2)
I remember some times ago it was discussion about this subject and some
people say that they put the business logic in the stored procedure and
some people say they do not but only code/dll.....

I have been praticipate in some projects where we put all login in to
stored procedure and it was relaible/readable and worder very good in terms
of perfomance as well

So the answer will be it depends on YOUR project's business logic and sure
if you can test 'somehow' and make the right decision

3) Well if you develop multi tier application the question is where to put
BL in data layer (dll that access to the database) or directly to stored
procedures
Again , I have seen many projects where people (including me) put the logic
into SP and some projects where people put the BL (including me) in the
code, so it is really DEPENDS on many things.

If you are lucky and Erlan ( and many others here at forum) jump in , it is
interesting to see what does he suggest ?

Show quote

"Will" <william_p***@yahoo.co.uk> wrote in message
news:1144324357.527781.15400@e56g2000cwe.googlegroups.com...
> Hi All,
>
> I've always tried to think of and use sql as being a tool to quickly
> retrieve / update relevant data, and let other parts of a system handle
> the decisions as to what needs to be done to the data. However I keep
> coming across (and sometimes find myself creating) the situation where
> there are massive stored procedures which will have several different
> statements, pulling data from loads of tables and updating others based
> on some business logic.
>
> First question: is this a bad thing?
>
> as I see it this has the advantage that updates can be expressed as a
> function to be applied over a whole table, making the process much
> faster than if the data was changed in a business layer, then
> propogated to the database. However the "code" is hard for someone else
> to understand, and hard to re-use/improve. I often find that processes
> which would be modelled with quite a large framework of objects are
> condensed down into a large stored procedure, such that to anyone else
> looking at it will just see a mass of update insert and selects with no
> idea why.
>
> Second question: when to do it?
>
> I have found that at times it's unaviodable, either for performance or
> simply the ease of access to all the data that I need to use a stored
> proc, does anyone have any rules of thumb as to when it's a good/bad
> idea?
>
> Third question: what are the alternatives?
>
> I'd hope that there are other ways to get around the problems that
> people are solving using sql, does anyone have any links/suggestions of
> how to approach things that people would often resort to sql for, using
> more maintainable methods?
>
> I could probably rattle on for days on this issue, but I'm hoping maybe
> people will be able to suggest some best practice about this.
>
> Cheers
> Will
>