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?

  • 3
    Schemas do seem like a good answer for this. Something else you could do along with that is changing the credential for each version of the app (one user per version) and setting a default schema for each.
    – JNK
    Jan 19, 2012 at 18:32
  • How do you currently trace your dependecies? In other words, how (at a macro level) do you see that Version2.SPROC1 depends on Version1.FUNC1 which depends on Version1.TABLE1? I mean aside from reading all of them manually... Also, I totally agree with @JNK and think the account idea is a great trick.
    – JoeGeeky
    Jan 21, 2012 at 11:46
  • I'm thinking that as we roll each version of the app, we create a full set of procs/functions with the new schema (version) name. Each of the dependencies are updated to point to the version from the new set. It's more work than I'd like, and it means that there are frequently unchanged copies but it's the tidiest and least confusing approach. Jan 22, 2012 at 5:44
  • How can this work? Different versions presumably have different functionality. Are you saying they work with the same data, same tables and columns? How can a single database switch around versions without changing the data?
    – ErikE
    Feb 12, 2012 at 19:40

2 Answers 2


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:

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

    DECLARE @cmd NVARCHAR(4000)

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

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


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.

  • This is kind of what I was thinking. Better yet, if your program sets the Application Name parameter in the connection string, you can switch based on that so you don't end up coupling it to your security design.
    – db2
    Feb 15, 2012 at 13:21

Somehow you need to maintain a list of the stored procedures that relate to each software version. There will be a pile of ways to do this, but the version/procedures will need to be documented clearly somewhere or maintaining the code base will be difficult (expensive) and end up buggy.

One method that springs to mind is to maintain a list of the procedures and their versions - as a database table or text list or web.config or compiled into your application. This list would have the procedure name and the version that it corresponds to (maybe with a base version) Then in the code you will need to write a method or class that will get the relevant procedures name as a string that you can return to the Data Access Layer (DAL) to use - I am assuming that the DAL know which query to run. To keep it simple, have a base list and then only put in "version" procedure entries where they are needed and get the most recent one.

You could take this a step further, and dynamically create the procedures that you need by passing the query text or variables (procedure names, table names) to be substituted into your query in the stored procedures at run time. ie: if procedure foo needs to call procedure bar.vn the pass bar.vn as a variable to procedure foo. This allows you to dynamically take into account different versions, but wont allow the database engine to effectively create statistics for that procedure. Here is a example of how to code something like this up http://www.codeproject.com/Articles/11739/Dynamic-Sales-by-Week-Procedure-in-SQL-Server

The best solution is to not have multiple versions at once, try to have the different functionality as different execution paths in the application (code or stored procedure). It will mean a few IF..ELSE bits of code, and what looks like a bit of work, but is much easier to maintain because the alternatives are in front of the developer - they do not need to know about which version needs which procedure.

  • I'd already thought about doing it this way, and it may be the way to go. I was hoping not to have to go to that much pain to do it, but I think this may be the option we end up taking. Feb 13, 2012 at 13:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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