I'm curious about people's approaches to using stored procedures in a database that is accessed by many applications. Specifically, do you tend to keep different sets of stored procedures for each application, do you try to use a shared set, or do you do a mix?
On the one hand, reuse of SPs allows for fewer changes when there is a model change or something similar and ideally less maintenance. On the other hand, if the needs of the applications diverge, changes to a stored procedure for one application can break other applications. I should note that in our environment, each application has its own development team, with poor communication between them. The data team has better communication though, and is mostly tasked with the stored procedure writing.
Thanks!
It all depends on your abstraction strategy. Are the stored procedures treated as a discrete point of abstraction, or are they treated as just another part of the application that calls them.
The answer to that will tell you how to manage them. If they are a discrete abstraction, they can be shared, as if you need new functionality, you'll add new procedures. If they are part of the app that calls them, they shouldn't be shared.