make server name as a variable

Question

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

Answer 1

Use dynamic SQL, however evil this may be:

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

Answer 2

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
}

Answer 3

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