How do I write portable SQL that refers to a linked server?

I've got a stored procedure that refers to a linked server. In several places throughout the procedure I've got something like the following:

INSERT INTO [TableName]
(...Columns...)
SELECT ...Columns...
FROM [ServerName\InstanceName].[Catalogue].[dbo].[TableName]
WHERE TableNameID = @TableNameID

This procedure exists in my Development environment, Test environment and Live environment.

The problem is that each copy of the procedure is subtly different because the server names are different for each environment. This makes managing deployment of script updates troublesome.

Is there a way to make the procedure portable so that each environment can run identical versions of it?

If not, is there anything I can do to make script deployment less prone to mistakes/errors?

The name of your linked server doesn't have to be the server's name. You can use a generic name.

EXEC master.dbo.sp_addlinkedserver
    @server = N'COMMONNAME',
    @srvproduct=N'MSDASQL',
    @provider=N'SQLNCLI',
    @provstr=N'DRIVER={SQL Server};SERVER=ACTUALSERVERNAME;UID=user1;PWD=rosebud567;', 
    @catalog=N'database1'

Set up the linked server on each environment with the same name, but actually point them at different servers.

StackExchange Database Administrators Q#25749, answer score: 14