sql - 如何使用表变量作为 OPENQUERY 的参数

Question

如何使用表变量作为 OPENQUERY 的参数

就像是:

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)

Answer 1

我找到了一个适用于我的案例的解决方案:

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)