How to handle stored procedure code changes from multiple applications using the same database?

I don't believe there is an elegant solution for that, but, as you cannot rewrite the entire application so... with your escenario, I'd duplicate all application's SPs and put them in separate schemas.

Create different users in your database and let each application connect to the db with its own user. The DB user for application A, its role, shouldn't have permissions to execute the SPs belonging to the other schema, and the same for the DB user for the application B.

You will need to rewrite some code of the applications to change the connection strings, of course.

Maybe each Application could have its own set of stored procedures, son they don't bother each other.

Are there reasons why you cannot assign default values to new parameters? e.g.

CREATE PROCEDURE xxx   @OldParam   int  ,@NewParam   int  = 0AS   <etc>

Application "A" passes in both parameters.

Application "B" was written when the proc only had one parameter, and only calls it with the one parameter that matters. With the new version, the second parameter will not be passed, so it picks up the default value of 0... and the procedure is coded appropriately. (NULL or special default values could be used to "flag" calls from older system.)

[Added]

A possible long-term solution would be to add some kind of version system to the database that gets updated every time the database is updated.

• When an application starts up, it gets the current version

• If there are calls whose parameters differ across versions, you check and format appropriately at the time of the call, such as

  If version < 3 then call proc with 1 parameter

  Else call proc with 2 parameters

This would be fussy and messy to track and maintain over time, but if you can’t update all systems (A, B, and the database) at the same time, your options are limited.