Dynamic Linked Server Query

问题:

Is it possible to construct a dynamic query to for a linked server (and if so how)?

For example:

@linkedServer varchar(50)
@var1 varchar(10)
@var2 varchar(10)

select * 
  from openquery(@linkedServer, 
                 'select c1,c2 
                    from t1 
                   where p1 = @var1 
                     and p2= @var2')

回答1:

example

exec ('select * from openquery(' + @linkedServer + 
', ''select c1,c2 from t1 where p1 = '' + @var1 + ''and p2= '' + @var2 + ''')

make sure to read The Curse and Blessings of Dynamic SQL to protect against SQL injection

回答2:

see EXEC() at Linked Server section of The Curse and Blessings of Dynamic SQL by Erland Sommarskog

from that article:

A special feature added in SQL 2005 is that you can use EXEC() to run pass-through queries on a linked server. This could be another instance of SQL Server, but it could also be an Oracle server, an Access database, Active directory or whatever. The SQL could be a single query or a sequence of statements, and could it be composed dynamically or be entirely static. The syntax is simple, as seen by this example:

EXEC('SELECT COUNT(*) FROM ' + @db + '.dbo.sysobjects') AT SQL2K

SQL2K is here a linked server that has been defined with sp_addlinkedserver.