Openquery with parameters

Question

I need to use Openquery to join a linked server. I am trying to filter the linked server by a string and I need to exclude the blank records.

DECLARE @Test1 NVARCHAR(10);
SET @Test1 = '111';
SELECT * from Test1 t1 
   JOIN OPENQUERY(TestServer, 'SELECT * FROM T2 WHERE Column1 = ''' +  @Test1 + ''' and Column2 != ''')t2 ON t1.Column3 = t2.Column3

Answer 1

Consider using sp_executesql according to https://learn.microsoft.com/en-US/troubleshoot/sql/admin/pass-variable-linked-server-query:

EXEC TestServer.master.dbo.sp_executesql N'SELECT * FROM MyDatabase.dbo.T2 WHERE Column1 = @Test1 and Column2 != ''''',  
    N'@Test1 nvarchar(10)', @Test1  

Answer 2

I have some tips on how to do this without losing your sanity in my article on dynamic SQL: https://www.sommarskog.se/dynamic_sql.html#remotedata.

Answer 3

Hi @Ludmil G
Please check this:

DECLARE @Test1 NVARCHAR(10);  
SET @Test1 = '111';  
SELECT *   
FROM Test1 t1   
JOIN OPENQUERY(TestServer, 'SELECT * FROM T2 WHERE Column1 = ''' +  @Test1 + ''' and Column2 != ''''')t2 ON t1.Column3 = t2.Column3  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.