including parameters in OPENQUERY

sql parameters openquery
gaponte69 picture gaponte69 · Jul 31, 2010 · Viewed 292.2k times · Source

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

Garett picture Garett · Jul 31, 2010

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

Related questions

Parameterize an SQL IN clause

How do I parameterize a query containing an IN clause with a variable number of arguments, like this one? SELECT * FROM Tags WHERE Name IN ('ruby','rails','scruffy','rubyonrails') ORDER BY Count DESC In this query, the number of arguments …

Read More
Can we pass parameters to a view in SQL?

Can we pass a parameter to a view in Microsoft SQL Server? I tried to create view in the following way, but it doesn't work: create or replace view v_emp(eno number) as select * from emp where emp_id=&…

Read More
Passing multiple values for a single parameter in Reporting Services

I have several Multi-Select parameters in my report. I am trying to find a way to pass in multiple values for a single parameter in the web query string? If I pass in a single value, it works fine. The …

Read More