Passing parameterized string to OpenQuery

OpenQuery can only accept a static string as an argument for the sql statement to execute over the remote link.

The only way around this is to execute the OpenQuery as a dynamic sql string and concatenate the parameters as part of the “static” string.

The results can then be piped into a temp table or table variable. The trick is that “SELECT INTO” will not work, so the table has to be defined beforehand to match the result set of the dynamic sql and use “INSERT INTO”.

The most obnoxious bit is the crazy nested tick-quotes…

DECLARE @mycodes AS TABLE (

 [id] [int] NOT NULL,

 [code] [varchar](25) NULL,

 [description] [varchar](80) NULL

)
declare @sql nvarchar(4000)

      , @myparm varchar(255)
SET @myparm = ‘someval’
SET @sql = ‘select id, code, description from openquery([MYDBLINK],”EXEC mydb.dbo.mystoredproc @myparm = ”” + @localval + ””” )’
print @sql
insert into @codes

EXEC sp_executesql @sql
select * from @codes