including parameters in OPENQUERY

From the OPENQUERY documentation it states that:

OPENQUERY does not accept variables for its arguments.

See this article for a workaround.

UPDATE:

As suggested, I'm including the recommendations from the article below.

Pass Basic Values

When the basic Transact-SQL statement is known, but you have to pass in one or more specific values, use code that is similar to the following sample:

DECLARE @TSQL varchar(8000), @VAR char(2)
SELECT  @VAR = 'CA'
SELECT  @TSQL = 'SELECT * FROM OPENQUERY(MyLinkedServer,''SELECT * FROM pubs.dbo.authors WHERE state = ''''' + @VAR + ''''''')'
EXEC (@TSQL)

Pass the Whole Query

When you have to pass in the whole Transact-SQL query or the name of the linked server (or both), use code that is similar to the following sample:

DECLARE @OPENQUERY nvarchar(4000), @TSQL nvarchar(4000), @LinkedServer nvarchar(4000)
SET @LinkedServer = 'MyLinkedServer'
SET @OPENQUERY = 'SELECT * FROM OPENQUERY('+ @LinkedServer + ','''
SET @TSQL = 'SELECT au_lname, au_id FROM pubs..authors'')' 
EXEC (@OPENQUERY+@TSQL) 

Use the Sp_executesql Stored Procedure

To avoid the multi-layered quotes, use code that is similar to the following sample:

DECLARE @VAR char(2)
SELECT  @VAR = 'CA'
EXEC MyLinkedServer.master.dbo.sp_executesql
N'SELECT * FROM pubs.dbo.authors WHERE state = @state',
N'@state char(2)',
@VAR

From the MSDN page:

OPENQUERY does not accept variables for its arguments

Fundamentally, this means you cannot issue a dynamic query. To achieve what your sample is attempting, try this:

SELECT * FROM 
   OPENQUERY([NameOfLinkedSERVER], 'SELECT * FROM TABLENAME') T1 
   INNER JOIN 
   MYSQLSERVER.DATABASE.DBO.TABLENAME T2 ON T1.PK = T2.PK 
where
   T1.field1 = @someParameter

Clearly if your TABLENAME table contains a large amount of data, this will go across the network too and performance might be poor. On the other hand, for a small amount of data, this works well and avoids the dynamic sql construction overheads (sql injection, escaping quotes) that an exec approach might require.


You can execute a string with OPENQUERY once you build it up. If you go this route think about security and take care not to concatenate user-entered text into your SQL!

DECLARE @Sql VARCHAR(8000)
SET @Sql = 'SELECT * FROM Tbl WHERE Field1 < ''someVal'' AND Field2 IN '+ @valueList 
SET @Sql = 'SELECT * FROM OPENQUERY(SVRNAME, ''' + REPLACE(@Sql, '''', '''''') + ''')'
EXEC(@Sql)