In your situation, you need a combination of linked server and synonyms. You have to have the linked server for the connection to the other system/database unless the other database is hosted locally which is also a possibility.

Since you cannot rely on the name of the other server or database, you would create the synonym the same, but then you can alter the synonym for each site. For example, if you deploy to site A and their remote server/database is called ServerA/siteA, and you deploy to Site B (serverB/siteB) and you deploy to site c (localserver/sitec), you will do the following:

At Site A - create the linked server to ServerA. Then you create the synonyms for the remote objects as:

CREATE SYNONYM remote.ObjectA FOR ServerA.SiteA.schema.objecta;

CREATE SYNONYM remote.ObjectB FOR ServerA.SiteA.schema.objectb;

...

At Site B - create the linked server to ServerB. Then create the synonyms as:

CREATE SYNONYM remote.ObjectA FOR ServerB.SiteB.schema.objecta;

CREATE SYNONYM remote.ObjectB FOR ServerB.SiteB.schema.objectb;

...

And for Site C - you don't have a linked server, so you create the synonyms as:

CREATE SYNONYM remote.ObjectA FOR SiteC.schema.objecta;

CREATE SYNONYM remote.ObjectB FOR SiteC.schema.objectb;

...

And, now - in your stored procedure you reference the above objects as:

SELECT ...

FROM remote.ObjectA

JOIN remote.ObjectB

...

The same code is deployed to each site - and the code stays the same. The only thing that changes is the synonyms for each site that will reference different servers/databases as defined at those sites. When your customers update the other server, you create a new linked server to the new server and drop/recreate the synonyms with the new server name and the code doesn't have to change.

No matter what, you have to have the linked server - but it won't matter what the names are and you can be compliant with your customers naming conventions without having to modify your code at all. Just modify the synonyms - your code references the synonyms.

I hope this clears it up for you, if not - post back and let me know if you need further clarification.