T-SQL Openquery with list parameter

Question

I would like to know how to pass a list into openquery. To use a single value the following will work:

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)

including parameters in OPENQUERY

However, I need to be able to use a condition like

WHERE state IN ('CA', 'TX', 'SD') 

rather than = 'CA'. I have tried many things with no luck. Help is appreciated.

I tried to use the below solution but it failed.

DECLARE @list NVARCHAR(MAX)
SET @list = 'value1, value2, value3' -- Replace this with your list of values

DECLARE @sql NVARCHAR(MAX)
SET @sql = '
SELECT * 
FROM OPENQUERY([YourLinkedServerName], 
    ''SELECT * 
      FROM YourRemoteTable 
      WHERE YourColumn IN (''' + REPLACE(@list, ',', ''',''') + ''')''
    )'

EXEC sp_executesql @sql

Answer 1

If you do a PRINT @sql, you will see that the dynamic query is

SELECT * 
FROM OPENQUERY([YourLinkedServerName], 
    'SELECT * 
      FROM YourRemoteTable 
      WHERE YourColumn IN ('value1',' value2',' value3')'
    )

which is wrong. You will need to double the single quote in the dynamic query

SELECT * 
FROM OPENQUERY([YourLinkedServerName], 
    'SELECT * 
      FROM YourRemoteTable 
      WHERE YourColumn IN (''value1'','' value2'','' value3'')'
    )

So when forming @sql, it should be

IN (''''' + REPLACE(@list, ',', ''''',''''') + ''''')'' 

Answer 2

I guess the real question is why are you using OPENQUERY in the first place?

You can query the Linked Server directly using four-part naming, no dynamic SQL needed.

DECLARE @VAR char(2) = 'CA';

SELECT a.*
FROM MyLinkedServer.pubs.dbo.authors a
WHERE a.state = @VAR;

For a list, you can use a table variable, a temp table or a TVP.

DECLARE @list TABLE (value char(2));
INSERT @list VALUES ('v1'), ('v2'), ('v3');

SELECT a.*
FROM MyLinkedServer.pubs.dbo.authors a
WHERE a.state IN (SELECT * FROM @VAR);