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.

  • 1
    do you mean you want both sprocs on the db at the same time and to be able to call both, but the problem is they cant have the same name?
    – Ewan
    Aug 6, 2019 at 12:02
  • yes thats what i want
    – Shanid
    Aug 6, 2019 at 12:51
  • This seems to be a problem you've caused yourself by using stored procedures. The obvious and simplest option is just to stop using them from now on and keep your SQL in your application. What motivation do you have to continue using them?
    – Nathan
    Aug 6, 2019 at 13:37
  • 2
    @NathanCooper This isn't a stored-procedure-specific problem and can happen without a database being involved.
    – Blrfl
    Aug 6, 2019 at 16:59
  • 1
    If your app does not provide for forward compatibility, and your database is unable to provide for backward compatibility, your only option is to force the end user to upgrade their app.
    – John Wu
    Sep 17, 2019 at 1:10

5 Answers 5


You have, broadly speaking, two choices:

  1. Make the stored procedure backward compatible,
  2. Add a second, v2, stored procedure to the database and leave the original unchanged.

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.

  • Unfortunately, some procedures have already been modified in the development environment. What can be done in that case ? Is there any way to handle this such that we do not need to change the procedure name ? For example, like changing schemas of procedures, is there any such way ?
    – Shanid
    Aug 6, 2019 at 12:53
  • 6
    @Shanid If the development database is being treated as a gold copy and can't be rebuilt into the same state from scratch, it would be wise to stop what you're doing and reconsider your development processes.
    – Blrfl
    Aug 6, 2019 at 17:02

Stored procedures don't support versioning. You will need to make a version of your database with two sprocs with the version in the name


Your API can support versioning in a number of different ways. so you can have two versions of the api deployed pointing to the same database

www.example.com/api/v1.0/endpoint - calls mySproc_v1
www.example.com/api/v2.0/endpoint - calls mySproc_v2

You should be versioning your database with source control and deploying with migration scripts. The general problem is that you generally want to share the db between different versions of your API, so it needs to be backwards compatible.

Sprocs are a good way to achieve this, the downside is you need to update the db with new sprocs all the time. You can simply not use sprocs and put the SQL in the api. This reduces the number of times you need to worry about the DB, but obviously if you remove a table column or something you will be in trouble.

  • Actually, some of the existing sprocs are already modified. Can anything be done in that case ?
    – Shanid
    Aug 6, 2019 at 13:52
  • 2
    roll them back by applying your migration roll back script (you did make a rollback script didnt you?.....)
    – Ewan
    Aug 6, 2019 at 14:10
  • Unfortunately no :(
    – Shanid
    Aug 6, 2019 at 14:27
  • How would you go about deleting the old versions automatically when not used anymore?
    – Zikato
    May 21, 2020 at 21:24
  • i wouldnt use sprocs
    – Ewan
    May 21, 2020 at 21:59

First and foremost, use the right tool for the job. Don't use the database to maintain versioning, use your source control tool (e.g. git) to maintain the revisions of the stored procedure in a regular flee and use your database migration tool to deploy new revisions of those stored procedures to the database (and if necessary, to remove old, unused ones). The "gold copy" of the stored procedure should be what's checked into your source control.

Second, separate your API versioning from stored procedure versioning. A Web API is generally public API, you probably have commitments to maintain those old versions for a certain period of time, stored procedure is generally a private interface, you'd want to deprecated outdated ones as soon as possible. If possible, you'd want to rewrite your v1 Web API so they use the same stored procedure as v2 internally rather than duplicating implementation. You'd want to design your APIs so it doesn't expose implementation detail, so that you can make these kind of internal changes without affecting old API users.


I don't know which database you're using, but is it possible to define stored procedures in different schemas (essentially namespaces) and give them access to the underlying data through synonyms? At least that's what I would try with Oracle.

If that's not possible, you could implement poor man's namespaces using prefixes, so doSomething() becomes v1_doSomething() and v2_doSomething().

Whatever you do, you will need to work a bit to get out of the hole you've dug yourself into...

  • Using mssql database
    – Shanid
    Aug 7, 2019 at 5:13

@hlie-ryan I like your way of maintaining versions for stored procedures using a single copy that s easier to maintain. How can the versions for many internal users be updated on the fly without breaking future versions when the removed versions are archived? I suppose you meant migrate only the stored procedures that are indictable to the version being currently implemented rather than all other versions.Some procedures may be unused and then you won't have implementation issues then :) My three cents!

Web API is great implementations for SOAP and XML processes as long as the legacy versions are deprecated. I.E Versions are thrown out and delink from other versions so that there is not any major breaks in the source libraries :)

Not the answer you're looking for? Browse other questions tagged or ask your own question.