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')
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With