In all of the decades that I've been working with SQL Server, many people have been preaching the benefits of using stored procedures, In all of that time, the vast majority of developers that I've worked with have not wanted to actually write, or even call them. In one job, I offered to write all procedures within a day of being asked for them for one development team, and they still didn't really want to use them. I made them do this by revoking permissions on many tables, but they still grumbled about the overhead of using procedures.
I was having a discussion with a friend recently about using procedure, and he was having a similar battle. There was a group of developers that wanted to embed SQL or use LINQ->SQL in their applications instead of calling stored procedures. It was frustrating, but he managed to secure a meeting to discuss the concerns with the developers.
As we talked, we tried to frame the problem from the perspective of a developer. Why would or wouldn't you want to use stored procedures? We had a few ideas, but really, I'm curious what you think today. Do you have any debate points that make sense from a developer point of view?
I certainly do understand the ease of just writing SQL in whatever IDE you use for C#, ASP.NET, Java, etc. I get not wanting to open up SSMS, which can be slow. I also get that it's nice to see all the code in one place, and not have some split between the application and the database.
On the other hand, the idea of encapsulating code in the database feels a lot like the reason developers refactor code into new classes and methods to provide a clean interface between different types of functionality. Many times developers work with applications and code split among different components. On top of this, when you use procedures you push a lot of the performance work down to the database and on the DBAs.
If you think stored procedures do or don't make sense from a developer point of view, let me know today. Leave a comment in the discussion and explain your position.
