Dynamically Specify Linked Server and DB Names in Stored Procedure

Question

I have the same query in a stored procedure that needs to be executed on different servers and databases according to parameters.

How can I arrange this without exec or sp_executesql?

I'm using SQL Server 2008.

Thank you.

UPDATE

I've found some links

http://www.eggheadcafe.com/software/aspnet/29397800/dynamically-specify-serve.aspx

http://www.sommarskog.se/dynamic_sql.html

Is using SYNONYM possible solution? If yes, than how?

UPDATE 2

I forgot to mention that all this servers are linked to the server where stored procedure is stored.

UPDATE 3

OPENROWSET or OPENDATASOURCE are not accessible either. I need a solution without building query string concating server name, schema name, db name.

It surely can be using if or case in stored procedure, but if we have 37 variations, then it's not a good solution.

Any other suggestions?

Answer 1

Does OPENROWSET or OPENDATASOURCE help?

EDIT: If it works, you can change the database at runtime & execute the query using the present connection. I cannot see any other way of executing query the way you want.

What is wrong with running query using string i.e dynamic query?

Answer 2

Nobody wants to answer, so I'll do it myself, just to have accepted answer.

There's isn't any way to do this. You need to use one of specified suggestions, anyway the query must be generating by concatenating.