I am maintaining an app that uses a lot of sql server 2008r2 stored procs, views etc.

The problem that I have is that certain stored procedures/views have code that is specific to the environment: production, test or development. That's how I inherited the system. The differences come from the way the linked servers have been defined.

What would be the best way to handle multiple versions of the same script?

Let's say I have a script containing a view called ViewName1.sql and this view is different in test than in production. Here are some options:

  1. Add the environment name to the file name: ViewName1.prod.sql, ViewName1.dev.sql, ViewName1.test.sql and place all scripts in the same folder.

  2. Put the code in different folders specific for each environment. So I would have something like this:

    • Dev
      • Views
        • VewName1.sql
        • ....
      • Stored Procedures
      • ....
    • Production
      • Views
        • ViewName1.sql
        • ....
      • Stored Procedures.

In this case I may put in Dev and Test only the scripts that are different, that is only the scripts that are specific to the environment, not everything. The production code would still be the source of truth for the rest of the code.

I like somewhat option 1 because the all the files that I have to modify are in the same place.

Any other ideas?


  • 1
    It would be better if you could parameterize the environments and use the same code. Using code for testing that is different from production code doesn't really yield a valid test. – Robert Harvey Jun 21 '16 at 23:14
  • @RobertHarvey: What do you mean when you say parameterize the environments? The problem are the linked servers. I totally agree with you on the second part. The previous developers should have made all the effort to unify the code. – costa Jun 22 '16 at 18:16
  • Parameterizing the environments means putting the things that need to change between environments (like connection strings) in some sort of configuration, and changing the configuration rather than changing the code. – Robert Harvey Jun 22 '16 at 18:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.