How can a Stored Procedure help you?

  • Article

I had an interesting discussion over lunch yesterday. Someone asked me to explain to them what Stored Procedures are and how their businesses could benefit from using them. Firstly, let me start by saying that I love Stored Procedures (sprocs). I developed Stored Procs for massive schemas at the Canadian Space Agency and they made my life way simpler.

What are they?

Stored Procedures is a subroutine acting as an API to the Database. Since I'm from Excel, my generalized explanation is that they are similar to UDFs. They are completely native to the database server; and as a result, execute complex queries and data access in a far more efficient way than client side code. I generally develop Store Procs using PL/SQL Developer. In summary:

  • They are pre-compiled (Queries)
  • Stored and Executed on the Database Server
  • Secured (if written properly using User Roles)
  • Simplify triggering complex queries

How Can your Business Benefit?

Stored Procedures are excellent subroutines for executing complex queries quickly and frequently. In many cases, they can benefit your business by:

  • Updating database tables using a triggered run
  • Manipulating Database information without providing users with direct access to the database (Security)
  • Creating complex queries and returning them to client side applications without overhead generally introduced by client apps
  • Porting pseudo real time data

If you would like to talk about specifics on Stored Procedures, please let me know. I'm always up for a good discussion.