Re: Need a Defense for SPs

Just replace the whole thing you just wrote with C++ (or another language) and say put it in the backend.
Unfortunately no one would get that.

"Jimmy Bond" <deeachem_at_netscape.net> wrote in message news:a5jukb$chq$1_at_slb3.atl.mindspring.net...
> A group at my company is trying to ban the use of Stored Procedures. Why?
> Well, these are the reasons that they cite for banning SPs:
>
> - In the past, some projects in which a database needed to either be
retired or
> migrated had problems doing this due to hundreds of unmanaged and
undocumented
> stored procedures that had built up over time (NOTE: This occurred with
some
> Sybase databases that were being migrated to Oracle)
>

How does this compare with unmanaged and undocumented Java,C++, or any other code? Undocumented, unmanaged code is not good. Agree on that, but just because it was in the database layer doesn't make it any more or less unmanaged. Anyone can write undocumented code. This is just an indication of poor programming practice in general; it has nothing whatsoever to do with stored procedures. Currently, I am trying to maintain and change a project written in Delphi. Something like 85,000 lines of code that I did not write, I have no access to the original engineers, there is no documentation and there are few comments in the code. Should I then assume that Delphi is a terrible language? (as a side note the marketing guy was told the code is self documenting. :-) ) No, of course not.

> - To keep application logic out of the data tier
>

GUI logic yes. I wouldn't expect display logic to reside in the database - well maybe a users color preferences etc. That's the perfect place for it. So instead they want to write it in all the applications that access the database and make sure the logic in all those applications is in synch.... So if you use multiple languages for application development then you have to repeat the same logic in different languages in multiple places. I have a phrase for that "Job security".

> - Because the company has no release management policies, guidelines or
> automation for stored procedures

Do they have them for non-stored procedures? Why not use those same policies, guidelines etc. It would be consistent, easy to start with. If they don't have them for C++ or Java or other computer code then this isn't an issue. If they do then use the same guidelines as much as possible. Code is code.

>
> - A company plan to build a Business Rules Engine sometime in the future
>

Not a good reason. It is not currently a project so it doesn't count. When I go to my Dr.'s for my annual checkup the excuse "I plan to lose 50 pounds sometime in the future." doesn't change the fact that I have a problem now. The task is still hard and if they haven't started it even as a viable project then it is vapor ware. Very complex; high risk, low gain.

> - To help future portability of the database to another platform
(presumably
> DB2)
>

So don't get what you pay for. Don't use that C++ compiler's optimization features because we might use another one at some unspecified future time. By having the logic in the database you actually make the application code easier. The interface is cleaner. Yes, the underlying code is different - the code in the database - since there is no stored procedure language standard.

>
> Yeah, I know this group's reasoning probably sounds like something out of
a
> Dilbert cartoon, but I need more reasons than simply calling the group
> techically incompetent to defend SPs and try to keep them from
establishing this
> as a company policy.
>
> Can anyone give some great reasons why SPs should be allowed, and even
> encouraged, in a development environment? (BTW, our projects' front-end
apps
> are usually Java or C++ based, sometime with a Mid-Tier Application layer)
>

If some query is busted or slow, I can modify the stored procedure and none of the appplications written in who knows what has to change. Also I can control data access to the database. I can control data integrity. I don't have to worry about some errant user pulling up MS Access and screwing things up - provided I don't give them a password that accesses tables directly.

I am asusming the data is important.
Jim
>
> (Thanks in Advance)
>
>
>
Received on Wed Feb 27 2002 - 20:18:34 CST