Creating Understanding and Educating Programmers, Developers and Technical Communities, one post at a time.

Thursday, June 18, 2009

Some thoughts on Stored Procedure

I read Paul Nielsen post on stored procedure. According to him stored procedures are good for the applications. He is not only talking about logical or business process stored procedure, but he also encouraged developer for CRUD stored procedures. I think, it is good idea. There is no harm to use them. If database server have that capability, then why not CRUD stored procedures. Here I am quoting his valuable opinion.

The only real solution is an abstraction layer that fully encapsulates the database. Every database fetch, insert, and update must go through this access layer. Just as SOA provides this encapsulation for processes, the database needs the same black-box API. If the database team wants to refactor the database to improve some feature, it’s free to do so. If a new feature is added, the database team can add that feature and modify the API. It’s very clean, easy to refactor, and the database is now an on-ramp to the corporate roadmap.

If we look at the history of database development language, you will find either PL/SQL or T-SQL, these languages are not changed. As Paul made a cogent argument about T-SQL that it is still same for the stored procedure, which written decade ago.

I will appreciate you comments. Feel free to share your advice.


  1. Read my first guest post on

  2. Hi Syed, Thanks for the thumbs up on stored procedures. I'm convinced that direct ad-hoc SQL is the worst possible database development practice. It's good to see otehrs agreeing. All the best to you and your family,