Sobre esto hay tantas opiniones como desarrolladores
El tema es como siempre que se habla de una herramienta o un recuso de cualquier clase: Depende de si te est útil hacerlo así, si produce beneficios e incluso si se trata de lineamientos obigatorios.
Mi caso es el tercero: Por requisitos de la empresa todo acceso desde una aplicación (cualquiera) a la base se debe hacer obligatoriamente por medio de SP.
Si bien por un lado es cierto que eso complica las cosas a la hora de realizar actualizaciones de servidor, por el conflicto de versiones, también es cierto que permite realizar un control de validaciones y administraciones de contingencias sin necesidad de intervención por la aplicación.
Para el caso: Como no se puede hacer uso de la aplicación para acceder a tablas de parámetros o validaciones, todas las validaciones se hacen de SP a SP, o de SP a SF, lo que también permite controlas las excepciones (si el lenguaje procedural lo admite), de modo que la respuesta de error o éxito se pueda estandarizar para la aplicación.
Proceduralmente, tiene muchas ventajas, además de aumentar considerablemente la seguridad por no ser vulnerables a ataques de sql-injection, por ejemplo, y que un usuario sólo requiere permisos de EXECUTE, o bien los apliques por perfiles...
En fin. es un tema largo con mucho debate, pero se resume a: Si te resulta funcional, eficiente y efectivo, usalos para todo. Sino, siempre tienes otros recursos...