How can I use a table variable as a parameter for OPENQUERY

Question

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)

Any good reason not to: `...WHERE state IN ('CA,'OR','WA')...`? — Amit, Jul 09 '15 at 22:05
i am totally with you @Amit. If you want to use parameters for dynamic SQL Code you should use `sp_executesql`. example for calling sp_executesql with parameters: https://msdn.microsoft.com/en-us/library/ms188001(v=sql.120).aspx — CeOnSql, Jul 10 '15 at 04:56
Hi Amit and CeOnSql, The example provided is a simplification. The real query is inside a Crystal Report and those values are not going to be fixed values. The user of the report will select those values on a prompt at running time, so they cannot be hard coded. — Francisco Jaramillo, Jul 10 '15 at 23:07

score 0 · Answer 1 · answered Jul 13 '15 at 17:51

I found a solution that works 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)

Use `FOR XML PATH` instead of the `SELECT @var=@var+...` trick. See https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/ — Razvan Socol, Jul 15 '15 at 20:01

How can I use a table variable as a parameter for OPENQUERY

1 Answers1