Inside my stored proc I have a SELECT statement which uses a table on a
server whose name I only find out when the proc executes.  I'd like to
pass this servername as an argument.  Is that possible (well, without
using dynamic SQL!)?

I tried:

DECLARE @servername VARCHAR (25)
SET @servername = 'myserver'
SELECT *
FROM @servername.dbname.dbo.tablename

-- but SQL Server won't accept that variable there ("Incorrect syntax
near '.'.")

Not that I know of, unless you can build a connection string dynamically and
pass that into OPENROWSET (but I have not tried this).  OPENQUERY will not
take parameters in the server_name slot, and T-SQL does not allow SQL Server
to resolve object names from variables at runtime unless you construct a
string and execute it dynamically.

Dynamic SQL is not the devil.  What do you expect to gain by avoiding
dynamic SQL in this case?  Do you think SQL Server would cache a plan for
every server you pass into the procedure?




Show quoteHide quote

Best way to insert data into tables without primary keys
What is new in Sql Server 2005 cursors?
Select Query Help Needed
SQL 2000 Database Performance
How does a view work