NYCPHP Meetup

[Paul A Houle]

  On 11/10/2010 4:48 PM, David Wang wrote:
> I'll second this.  The problem i've always had in the past with sprocs 
> is that it adds another layer of abstraction to the business logic and 
> difficult to manage changes for.  I've always told my developers to 
> move it out of the sprocs and into the code so we see what's going on 
> and can manage changes in a code repository.
>
> The only time i've bit the bullet and moved logic into sprocs was when 
> there was a mixed language environment.  Instead of duplicating logic, 
> we put the logic in the database using sprocs so that both languages 
> had access and logic didn't have to be duplicated.
     Most of the system I've worked on do not use sprocs,  or only use 
them in a minor way.  I have worked on two projects that were heavily 
sproc-based,  and it really wasn't that bad.

     We addressed the version control problem by using migration scripts 
that worked,  more or less,  like migrations in Ruby On Rails.  This 
gave us acceptable version control,  although of course we didn't get a 
complete change log from our VCS.  (It wouldn't be much effort to build 
some script that would help with this,  however)

     I've used sprocs quite a bit in MS SQL server to "add commands" to 
do things that are a pain to do in the SQL monitor,  for instance

http://gen5.info/q/2008/06/06/how-to-drop-a-primary-key-in-microsoft-sql-server/

---

     Note that a possible architecture for a db app is to have a 
class/source code file/folder that has all of your SQL statements in 
it,  wrapped up in functions.  An app built like this looks a lot like 
one that's based on stored procedures,  but has the advantage that it's 
all written in one language.  It's also easy to make a system like this 
compatible with different database systems by swapping out different 
versions of this class.  (Wrappers like this are also logical to put 
around real sprocs too...)

     Of course,  this architecture and the sproc-based architecture is 
incompatible with the popular ORM-based architecture,  which takes it's 
own approach to the database abstraction problem.