One thing I did not see you mention as that stored procedures can cause duplication of implementation.
In a reporting site we were making we needed to get many sums and counts grouped by either month, area, country and/or some other values. When joining the team there were about 150 stored procedures all with (for example):
JOIN [Calendar] on Entity.Year = Calendar.Year and Entity.Month = Calendar.Month
And something like:
SELECT ..., [Calendar].[Month] ...[the JOIN] ... GROUP BY [Calendar].[Month]
Or when filtering (filtering does not need to select the group):
SELECT ..., [the JOIN] ... WHERE [Calendar].[Month] IN (....)
When adding the option to filter and group by quarterly value another 150 stored procedures needed to be created or 150 stored procedures needed to be changed since the join and group were copy pasted in all the 150 procedures.
I added a SQL builder class and DataLayerHelper types to build a SQL statement that inherits from BaseDataLayerHelper so adding grouping by quaterly value would require me to only change one function; the BaseDataLayerHelper.prepareGroup (pseudo code):
prepareGroup(Query q, dataType, groupType){
.....existing code
if(groupType == BaseDataLayerHelper.GROUP_BY_QUARTER){
IField dateField = this.joinCalendar(q, fromTable, groupType);
((Field)dateField).group = true;
((Field)dateField).sort = "ASC";
q.addField(dateField);
}
}
This would never be possible using stored procedures. Any new grouping or filtering feature that needed to be applied to all or many entities can now be implemented in the BaseDataLayerHelper.
Some people would still argue stored procedures is better because of performance (even though performance is not a problem) and because things get done quicker when people can use stored procedures.
Maybe sprocs for new things (new entities) get done quicker because some people in the team do not know how to use the helper and SQL builder but implemening application wide new filtering and grouping options would definetely take a lot longer with stored procedures.
It is funny though that even though in this particular case; using strored procedures was such an obvious Maintainability and extensibility problem, some people would still stubbornly vote to use them.
?