Dynamic SQL Passthrough Queries with Parameters

If you need to run a passthrough query against another DBMS and need to create that SQL on the fly because of a parameter, then you may be out of luck. At least, according to MSDN:
http://msdn.microsoft.com/en-us/library/ms188427.aspx

However, there is a nice workaround you can employ that will do the job. Here is an example:

-- variable to contain the passthrough sql statement
DECLARE @SQL VARCHAR(300)
-- variable to contain the dynamic lookup value into the query
DECLARE @KeyLookup INTEGER
-- varliable to contain the passthrough query
DECLARE @query VARCHAR(400)
-- populate the keylookup value
SET @KeyLookup = 102
-- prepare the sql statment for the passthrough query
SET @query = 'select * from some_table where key_value = ' + CONVERT(VARCHAR(10) , @KeyLookup)
-- prepare the passthrough execution query
SET @sql = 'select * from openquery(linkedservername, ''' + @query + ''')'
-- option to print the passthrough execution query in full for debug purposes (this is nice because you can paste it into a sql
-- editor and run it to see what is wrong)
PRINT @SQL
-- execute the passthrough
EXEC (@SQL)