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?
SELECT <fields> FROM <linked server>
but use the same view name across all servers to keep code maintained