How can I run multiple concurrent versions of my application against the same database? 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?
Interesting mess. Multiple options if you're looking for model refactoring (recommended). If it's not the case/there are time constraints you can try to exploit EXEC. There's neat feature that is not very popular (maybe it's good). Core concept:

DECLARE @cmd NVARCHAR(4000)
SET @cmd = N'dbo.YourProcedure_v11'

DECLARE @retcode INT
EXEC @retcode = @cmd @Param1 = 1,
                     @Param2 = 2,
                     @ParamN = 'N'


You have to create stub procedure ('master' accessed from DAL) and call your procedures depending on user e.g.:

CREATE PROCEDURE dbo.YourProcedure
(
    @Param1 INT,
    @Param2 INT,
    @ParamN CHAR(1),
    @UserID INT
)   
AS
BEGIN

    SET NOCOUNT ON
    DECLARE @cmd NVARCHAR(4000)

    IF @UserID = 1
        SET @cmd = N'dbo.YourProcedure_v11'
    ELSE
        SET @cmd = N'dbo.YourProcedure_v12'

    DECLARE @retcode INT
    EXEC @retcode = @cmd @Param1 = @Param1,
                    @Param2 = @Param2,
                    @ParamN = @ParamN
    RETURN @retcode

END


Instead of passing UserID every time/to all procedures - you can use suser_sname etc depending on your security model. Also user-version mappings should be somewhere in database - stored procedure names are hardcoded only for testing.