• Because, by varying the server name, you are varying the object you a referring to, that is dynamic SQL.

    Except, we have objects that "alias" to other objects .. views to tables, linked servers to servers.

    So you could create a view that references what you want. Although creating the view is dynamic SQL.

    I have dynamically created linkedservers. Actually I setup some linkedservers, passed the name of one of them to a SP, which made a copy of the linkedserver under a different name, so it is effectively a variable linked server. No dynamic SQL is required because everything is parameters to sp_addlinkedserver.

    An obvious problem is contention for the 'variable' linkedserver - two processes trying to use it differently. Its only appropriate for overnight sorts of things, and even then I created a locking record.