title: How can I conditionally use a linked server depending on the environment a stored proc is currently running in?
body: <p>Here's the issue I'm having. I am trying to create a stored proc that will be deployed to DEV, QA, and PROD environments. Because of the strict requirements on the deployment process, I have to make sure my proc in the same across all three environments and has to work (of course!). The problem is that this proc references a table in a different database. In DEV and QA this is ok, because the database is on the same server, however in PROD the database in question is located on a separate server. The following is a code snippet from my proc that tries to deal with the different environment issues:</p>
  
  <pre><code>IF @@SERVERNAME&lt;&gt;'Production'
  BEGIN
    select distinct m.acct_id 
    from l_map m (nolock)
    join #llist ll on ll.acct_id = m.acct_id
    where ll.acct_id not in (select l_number from [OTHERDATABASE].[dbo].[OTHERTABLE] where lmi_status_code not in (select item from #ruleItems))
  END 
  ELSE
  BEGIN
    select distinct m.acct_id 
    from l_map m (nolock)
    join #llist ll on ll.acct_id = m.acct_id
    where ll.acct_id not in (select l_number from [OTHERSERVER].[OTHERDATABASE].[dbo].[OTHERTABLE] where lmi_status_code not in (select item from #ruleItems))
  END
  </code></pre>
  
  <p>My proc is called from within a different proc. When I test the above logic directly, I get the results I expect. However, when I try to test it in context in DEV or QA (from the top level proc), I get an error saying that [OTHERSERVER] could not be found. I cannot (and don't need to) create a linked server in DEV and QA, but I need to be able to use the linked server in the PROD environment. Does anyone know how to accomplish this?</p>