make server name as a variable

I am trying to get data from different sever, and the sever name might change. So I set the server name as a parameter. The idea of my sql store procedure is something like this

CREATE PROCEDURE [dbo].[GetData]
        @ServerName
AS
BEGIN
    SELECT * FROM @ServerName.ClientDataBase.dbo.Client
END

Does anyone know how to achieve this?

The database version is sql server 2005

From stackoverflow

• Use dynamic SQL, however evil this may be:

  exec('select * from ' + @ServerName + '.ClientDatabase.dbo.Client')
  

• Look at using Synonym(s)

  Create syntax (from MSDN):

  CREATE SYNONYM [ schema_name_1. ] synonym_name FOR < object >
  
  < object > :: =
  {
      [ server_name.[ database_name ] . [ schema_name_2 ].| 
           database_name . [ schema_name_2 ].| schema_name_2. ] object_name
  }
  

  gbn : Good idea- Works for a limited number of server names

  uosɐſ : It seems to me that both the CREATE SYNONYM and the sp_setnetname suggestions here might have unintended consequences with concurrency since the operations have a global effect. That is, if two such operations overlapped, one or both of the operations might fail and leave the server configuration in an inconsistent state.

• You could set up a linked server, says "BOB".

  Then you could call sp_setnetname to change the underlying target server but keep the name BOB.

  So your code would be:

  CREATE PROCEDURE [dbo].[GetData]
          @ServerName
  AS
  BEGIN
      EXEC sp_setnetname 'BOB', @ServerName
      SELECT * FROM BOB.ClientDataBase.dbo.Client
  END
  

  John Dibling : You would need to drop the linked server too, unless you run this SP only once.

  gbn : Why? sp_setnetname allows you to redefine the linked server target