Monday, May 19, 2014

Why Use Stored Procedures?

It's an old debate. Google 'why use stored procedures' and you'll get a million hits and a million opinions.  Many of the answers are technical in nature, relating to performance issues. The rest are pro/con discussions about where and how you want to code and debug your business logic, or how ugly stored procedures are compared to an elegant ORM. Along those lines, here is my favorite rant Who Needs Stored Procedures, Anyways?

But most of those reasons don't really matter. because the most compelling reasons to use sprocs are not technical at all, they are administrative.

Reason 1:
Data integrity. Say for example, your app has 1,000 tables. Don't laugh - SAP has way more tables than that. Your shop has a dev pool of 6 positions with an average job turnaround of 6 months. If those developers are maintaining embedding SQL into the application code, inconsistencies will crop up. If you are using sprocs, then you can have some assurance that the DAL isn't impacted.

Reason 2:
The business. Remember them? They are why you have a database. Suppose you have a business role that audits business line items, but isn't allowed to view the cost field in the line item table. Table level security won't help. Even if your app doesn't expose the cost, a clever power user will figure out how to download the table in MS Access, and then they can view the cost. You need sprocs or views to control that.

In light of business reasons like these, does it matter that stored procedures can't pass objects? Only to coders.

No comments:

Post a Comment