Recently I read two blog posts about stored procedures. Personally I like writing stored procs. However I am sometimes hesitant to do so because of the difficulty in releasing the software to clients. It is simple to write all the code in C++. We have an single installer which deploys the application on the workstation. If I include part of my solution in a stored procedure, I need to make sure the client and back end stored procedures are in sync. That is one extra headache that makes me choose the easier route more times than not.But let’s get back to the debate on stored procedures. One author stated that placing code in a stored procedure does not give you a performance advantage per se. That is because normal queries from a client get cached and have equal performance. I am not sure if I agree. A stored procedure which has been compiled into the database is going to have some inherent benefits for performance, especially if there is a lot of SQL involved.
Stored procedures stop injection attacks. They also enforce data integrity if you choose to do so. I recall from my Oracle programming class that you should use constraints to enforce integrity, not stored procs. However that might be a personal decision. One author said that you should not code business logic within a stored procedure. Again I don’t think I agree. Your business layer can be at the stored procedure level.
When you do code logic in a stored procedure, you are normally locked into a particular database vendor. That’s not a problem on my current project. We are an Oracle shop, and will most likely be so until the end of time. One benefit with stored procedures are that you can have fine control over the permissions on who can execute a stored procedure. That is one ability that I like.
