The other day I was debating the need/use for stored procedures in a modern MVC web application with a colleague of mine. His argument is that ORMs are “stupid” and the best way to separate data (model) from the application is to use a stored procedure. The stored procedure can be wrapped in a model type class which can be loaded by the controller. I have a few issues with this.
- You are coupling your application to a specific database. Even if you are writing standard ansi SQL. Which is completely compatible with any ansi compliant database you still have to export and import the procs if you want to change the database.
- You have increased your debugging complexity. If a problem occurs I now have to determine if it is in the stored proc or the program. I know that proper logging and error capturing can alert you to where the problem is occuring but you still have to address it in another layer besides the application
- Databases were designed and best suited for storing and managing data. Not performing logic on that data. That is why we have programs. IMHO
Now, having said this I do realize that in some cases stored procedures are completely warranted. Like when you have a rather complex series of queries based on frequently changing parameters and it would be best to compile it and store it in the database. My opinion is that “most” web applications shouldn’t need to use stored procedures to separate the business logic from the application.