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

Question

The database in our environment is being shared by 2 different environments/applications. Out of the 2 applications , first one -lets say A, gets updated frequently, while the 2nd application B does not get updated that often.

So here the situation is when the application A get updated with new code, mainly stored procedures, it can sometimes affect and break 2nd application B, which is being updated by another team, and not updated too often. I understand that this should not be the right way to handle it and not updating both environments together can be disastrous. This is happening because the the applications A & B are being handled by different teams.

How would you handle this situation elegantly?

From Application B the precautions I could take are - Retrieving data in code - The best way while retrieving data would be to check for blank/null columns, so that if new columns are added by application A, they can be ignored by application B. Retrieving data in SQL - Within the procedure, it can be handled using optional parameters.

But when C# code calls a procedure, we are supposed to be passing param values, and if there are new parameters added, then it breaks. Is there a way to make sure that if the calling parameter is missing, then it should be ignored(from C# or SQL Server)?

My research shows -

1. that I can retrieve the list of parameters from the sproc first, and then call the procedure using that list to loop through and check if the param exists. So that even if the application A adds new params, it can be automatically handled by application B. This could be done using DeriveParameters in C# or a SQL query to get the param list.

2. Change all SPROCs to take parameters in the form of a CSV. And split them in the SPROC and use it accordingly. This sounds lame for hundreds of existing sprocs.

Again, as I said - this is doesnt look like a good solution, and if you had a similar situation, how differently would you have handled it? Is there a framework out there, which I dont know of which could work around this scenario?

Environment - ASP.NET/C# 4.0, SQL Server 2008 R2

EDIT - Let me rephrase and provide a little more details here.

1. Application/Team A changes the code, but only C# code gets rolled out to the production yet, not the DB changes. This is where the difference is. And the DB code would go in only with next release.

2. Application/Team B has the latest code, but the DB changes are not in production yet, and still uses the old database.

Answer 1

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.

Answer 2

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

Answer 3

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

CREATE PROCEDURE xxx

   @OldParam   int
  ,@NewParam   int  = 0

AS

   <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.