Why use stored procedures?
A friend was just asking me online if I use stored procedures for everything in my current application. The answer was yes, and it deserves noting that there are two good reasons to be in this habit.
The first reason is rather obvious, they are far more secure against SQL Injection Attacks. I don’t feel like going into that right now, so I have provided the link for those who want to know more.
The second reason is that it seperates the database schema from the application. This makes it easier to deal with changes to the database. It eliminates the need to dig through your application and find all of the instances where an ad hoc SQL statement is accessing the database.
I just ran into an issue in my application that is causing me to change databases from SQL Express to Access. I’m not thrilled about it because I feel I am taking a step back in technology, but it’s about what the customer wants, not what I want and in this case they want an install that doesn’t require them to load SQL Express. I currently have 59 stored procedures and 2 views I am converting to Access. In my application those procedures are called multiple times each. Thanks to my use of stored procedures though, I will simply be changing the database connection in my application and cleaning up a few small issues that will be inevitable.
Whether you are security conscious or not, using stored procedures (or in Access, queries) can be extremely helpful. I would recommend using them wherever you need a query. If you don’t have the option to use them, create a class in your application to at least group all of your SQL code together. You can ensure on your own that your security is right, and you can more efficiently manage your SQL statements (although stored procedures are still better).