9. July 2007 21:17
This post is in response to Frans Bouma's post, where he explains why Stored Procedures are bad. I'm not going to go over the differences of why to use Stored Procedures or why to use In-Line SQL, you can find many opinions on the topic by doing a simple search. I'm just going to reply to his post with my opinions.
One of the reasons why Frans likes to use Ad-Hoc (In-Line) SQL is because with SPs you have to write one SP for everything you do, which can become very annoying to maintain. I agree with him. Then he goes on criticizing Rob Howard for not knowing what Role Based security is and saying that you can replace the security benefit of using SP by using views. Then my question to Frans is the following, if one of the reasons for not using SPs is because of the maintenance nightmare, what do you think will happen with the views? You're going to get the same maintenance nightmare as with the SPs. Also SPs bring an additional security benefit that In-Line SQL doesn't. You can give a user (or role/group) execute permission to a SP without having to give that same user read access to the table. Now that is very important! Imagine an application that uses a very restrictive security model, where the user connecting to the DB only has access to execute SPs and no access to read, insert, update or delete on any tables. Unfortunately this application was poorly coded and is vulnerable to SQL Injection. The hacker will not be able to read any tables, nor delete, update, or insert data. How will you accomplish that with in-line SQL without having a maintenance nightmare with the Views?
One of the rules of securing an application is to have multiple levels of security and not to rely on only one level. By using SPs and only giving the users access to execute them you are adding an extra layer of security, which would have not been there with In-Line SQL and our SQL Injection vulnerable application would have been used by a hacker to get what he wanted.
Thus far I haven't found a really good reason (or sets of reason) of why to use In-Line SQL instead of SPs so far now I will continue to use them.