including parameters in OPENQUERY

Question

How can I use a parameter inside sql openquery, such as:

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

Answer 1

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 

Answer 2

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.