Something Like:

DECLARE @TSQL VARCHAR(8000)

DECLARE @VAR TABLE (VAR1 VARCHAR (2))

INSERT INTO @VAR values ('CA'),('OR') ,('WA')

SELECT  @TSQL = 'SELECT * FROM OPENQUERY(MyLinkedServer,''SELECT * FROM pubs.dbo.authors WHERE state = ''''' + (SELECT VAR1 FROM @VAR)+ ''''''')'

EXEC (@TSQL)

OPENQUERY does not accept variables for its arguments.

You need to do something like this:

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)

You cannot use parameters with OPENQUERY, but you must provide a complete query string. Furthermore, table-valued parameters does not make sense in the context of a linked server, since this is a proprieatry feature to SQL Server. Recall that a linked server can be any data source, Oracle, Access, you name it.

For this particular case, you would to construct a comma-separated list:

SELECT @csv = (SELECT quotename(VAR1), '''') + ','
               FROM   @VAR
               FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')
SELECT @csv = substring(@csv, 1, len(@csv) - 1)

SELECT @query = dbo.quotestring('SELECT * FROM pubs.dbo.authors WHERE state IN (' + @csv + ')', '''')

SELECT @sql = dbo.quotestring('SELECT * FROM OPENQUERY(MyLinkedServer' + @query + ')', '''')

EXEC (@sql)

See also http://www.sommarskog.se/dynamic_sql.html#OPENQUERY

Thank you Andy and Erland, your help is very appreciated.

I spend some time learning from Erland solution and then I found this solution which worked for my case:

DECLARE @query VARCHAR(8000)
DECLARE @list VARCHAR(8000)
DECLARE @len int
SET @list = ''
DECLARE @var TABLE (var1 VARCHAR (30))
INSERT INTO @var values ('2015-01-01 00:00:00.000'),('2015-01-02 00:00:00.000') ,('2015-01-03 00:00:00.000')
SELECT @list = @list + ISNULL(var1 + ''''',''''', '')
FROM @var
SET @len = len(@list)
SET @list = ''''''+left(@list,@len-3)
SELECT @query =
'select *
FROM openquery([REMOTESERVER],
''
select *
from [DATABASE].[SCHEMA].[TABLE]
where FIELD in (' + @list + ')
'')'
EXEC (@query)

• Marked as answer by Katherine XiongMicrosoft contingent staff, Moderator 5 hours 36 minutes ago

SELECT @list = @list + ISNULL(var1 + ''''',''''', '')
FROM @var

Note that the correct result of this query is undefined, and it may not produce what you expect.

The same form to run string concatenation is to use FOR XML PATH. By no means an intuitive syntax, but it works with guarantees.

SELECT @list = (SELECT quotename(var1, '''') + ','
                FROM   @var
                FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')