including parameters in OPENQUERY

user_275062 0
Viewed 346816

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
openquery parameters sql
14 Answers

We can use execute method instead of openquery. Its code is much cleaner. I had to get linked server query result in a variable. I used following code.

CREATE TABLE #selected_store
(
   code VARCHAR(250),
   id INT
)
declare @storeId as integer = 25
insert into #selected_store (id, code) execute('SELECT store_id, code from quickstartproductionnew.store where store_id = ?', @storeId) at [MYSQL]  

declare @code as varchar(100)
select @code = code from #selected_store
select @code
drop table #selected_store

Note:

if your query doesn't work, please make sure remote proc transaction promotion is set as false for your linked server connection.

EXEC master.dbo.sp_serveroption
       @server = N'{linked server name}',
       @optname = N'remote proc transaction promotion',
       @optvalue = N'false';
user_35902830 
DECLARE @usernames NVARCHAR(MAX);
SET @usernames = N'';
DECLARE @len INT;
SELECT @usernames = @usernames + ISNULL(username + ''''',''''', '')
FROM #tempusername;
SET @len = len(@usernames);
SET @usernames = N'''''' + LEFT(@usernames, @len - 3);
DECLARE @sql NVARCHAR(MAX);
SET @sql
= N'SELECT *
FROM OPENQUERY
     ([Linked Server],
      ''SELECT username  
       FROM [MySQL Database].[Table Name]
       WHERE username IN ( ' + @usernames + N')'')';

I found this workaround on this link https://social.technet.microsoft.com/Forums/en-US/98e0c17a-8ead-4633-a053-51c49b313bd3/how-can-i-use-a-table-variable-as-a-parameter-for-openquery?forum=sqltools&prof=required it was very helpful.

user_143484560

Just try it this way, should work, easy! In your WHERE clause, after column name and equal to sign:- add TWO single quotes, your search value and then THREE single quotes. Close the bracket.

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

user_77441220

Simple example based off of @Tuan Zaidi's example above which seemed the easiest. Didn't know you can do the filter on the outside of OPENQUERY... so much easier!

However in my case I needed to stuff it in a variable so I created an additional Sub Query Level to return a single value.

SET @SFID = (SELECT T.Id FROM (SELECT Id,  Contact_ID_SQL__c  FROM OPENQUERY([TR-SF-PROD], 'SELECT Id,  Contact_ID_SQL__c FROM Contact') WHERE Contact_ID_SQL__c = @ContactID) T)
user_18194030
Related