Background: I have multiple concurrent versions of my application running in my production environment. Depending on the user account that's used, the user will have access to a different version of the software.
Environment: Currently SQL Server 2005, imminently migrating to SQL Server 2008, ASP.Net
Problem: Each version of the software may or may not use different versions of the stored procedures that interact with the data in the database. Currently when a version is changed, whoever changes it creates a new copy and appends an incremental version number to the end. At this point we have numerous versions of some stored procs and only one version of others and nobody is sure which version of the application is pointing to which version of the stored procs - it's a mess.
I'm looking for a solution that will neatly package the stored procs up for any given version of the application to be used as the basis for a new version. This means that the new version of the application can be pointed to the new set of procs which can be rewritten or modified to the end user's content without affecting other versions of the applications that are currently available in production.
I originally thought about schemas, but one part of the issue is that the procs are heavily coupled with other procs and user defined functions, so when copying to another schema, we'd new to map and replace all these links which isn't ideal. Is there a way to write the procs so that moving them to another schema would bring the relationship between them along for the ride so that for instance if Schema1.Proc1 referenced Schema1.Proc2 and we copied them to Schema2 that Schema2.Proc1 would now reference Schema2.Proc2 without having to do any additional work? How would this handle user defined functions where you have to reference the function by SchemaX.Func1()? It would be handy if I could refer to the function without defining the schema the function belonged to in the same way I can with a stored procedure, but I don't think that's possible.
It seems like this is a problem that should already be solved, but I don't know what I'm looking for in order to find a viable solution.
Does anyone have any ideas?