Is there any way to create versioning for stored procedures? I have created an API which fetches data from a database using stored procedures. This API is being consumed from a mobile app. I released the v1.0 of API (www.example.com/api/v1.0/endpoints). Now I've to release the v2.0 of the API (www.example.com/api/v2.0/endpoints), and there are many changes both in the API and stored procedures. So how do I release the v2.0 without affecting the older API? Because even if API is independent, stored procedures are the same for both. How is this kind of situation supposed to be handled?
I mean I want both sprocs on the db at the same time and to be able to call both, but the problem is they can't have the same name.
Best Answer
You have, broadly speaking, two choices:
The first approach can be harder to implement. You may need default values or parameters and complex logic in the stored procedure to handle both the old and new behaviours. Such a stored procedure might be harder to maintain as a result. If not, then you have the advantage of only having one stored procedure still to maintain in the future.
The second approach is often far simpler to implement. Just create a new stored procedure and leave the old one unchanged. But then you have two versions to maintain. A bug might be found that affects both, requiring both to be fixed. And when you come to v3, v4 etc you risk an ever increasing set of similar pieces of code to maintain.
Which approach will suit your specific case really depends on the scope of your changes. You will likely want to pick the approach that will make future maintenance easier even if it makes the change harder to implement initially.