Stored Procedures in Databases
October 22, 2008
I read a very interesting article about stored procedures (Whats [sic] up with stored procedures these days?). Some points were raised against using stored procedures, which prompted me to write this post.
I am a big fan of databases. In fact, I am a big fan of stored procedures. The reason is that if I need to manipulate data, then it is easier for me to do so on the database itself, mostly using SQL (or only using SQL). Sure, one can use inline SQL or other, newer, fancier tools within your application code, but if you have something like PL/SQL or T-SQL, it already provides much of what you would like to do. They surely aren’t the solution to everything, but for most cases they are great.
I agree that business logic should be separate from data. However, in a well-normalized database, data is stored mostly in conjunction with logic. Let me put it another way: whatever data you store, the data itself contains a lot of business logic. If an invoice goes with a customer (business logic), you store these two values together in an RDBMS, not separately. So if not all business logic, at least a lot of it is within the data. Stored procedures take it a step further and solidify these relationships (logic) as a presentable view (not to be confused with a database ‘view’) to the outside world.
Application portability is addressed often times when it comes to stored procedures. If all your data logic is stored in an application, you can port the application from one DBMS to another, but it is difficult to implement same application in two or more technologies. For example, you have an application written in Java, and then you need to have a similar (if not exact) application written in Python, with the same database back-end. Now you have to take the data logic and re-implement it in Python. If the same logic had resided in the database as stored procedures, you could just pass parameters to them from any number of applications made in any number of technologies. Now your application becomes portable across applications rather than across databases. If you business logic needs to be tweaked, you make these changes in one place and every application is now updated without actually changing the application code.
Obviously, this doesn’t solve the problem of portability across databases. But if you are changing the database, you might have to change your application with all its SQL anyways (to some extent, at least). This brings me to another point. With much SQL or data logic within the application code, you lose the ability to keep data storage separate from your code. As in the case of multiple databases, your application now needs to connect to them individually to get its data. Meanwhile, with linked servers and stored procedures, you call a stored procedure without having to know how the database layer has been implemented. You could change that implementation without affecting the application. You could have a cluster or other high availability or load sharing mechanisms, and change them around, all the while the application only knows about the stored procedures it needs.
I don’t think either one of the solution is perfect for all situations. Sometimes it may be better to use stored procedures and sometimes not so much. I would rather use stored procedures than not. It may be because I am more comfortable working with databases. When there is an issue of control in a team, where both the DBA and the developer are fighting on controlling the solution to their liking, then it is not a debate on the merits of stored procedures but more of a human problem.
Disclaimer: this post may be rambling on, but I wanted to get my initial reaction across. As I think more about this issue, I will refine what I am saying.