Including list of params in OPENQUERY

Question

I'm trying to get the values from an external Database server executing the following code:

DECLARE @TSQL VARCHAR(8000), @VAR CHAR(2)
DECLARE @Prefixos VARCHAR(MAX);

WITH cte AS 
(
    SELECT DISTINCT prefixo 
    FROM ARH.arh.UorPos
)
SELECT @Prefixos = COALESCE(@Prefixos + ', ', '') + prefixo 
FROM cte 
ORDER BY prefixo
--SELECT @Prefixos --print a list of values separated by comma. eg.: 1, 2, 3

SELECT  @TSQL = 'SELECT * FROM OPENQUERY(DICOI_LINKEDSERVER,''SELECT * FROM ssr.vw_sigas_diage where cd_prf_responsavel in ('''''  + @Prefixos + ''''''') order by cd_prf_responsavel, codigo'
EXEC (@TSQL)

But I'm getting:

OLE DB provider "MSDASQL" for linked server "DICOI_LINKEDSERVER" returned message "ERRO: syntax error at the end of input;
No query has been executed with that handle".

Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "DICOI_LINKEDSERVER".

I've researched the above links to try to resolve it:

• How to concatenate text from multiple rows into a single text string in SQL server?

• including parameters in OPENQUERY

• Join query result to a single line of values separated by comma [duplicate]

Can anyone help me to resolve it ?

Thanks in advance.

Answer 1

Before running your query with EXEC, it's best to just check first how the generated SQL string will look like.

For example by selecting the variable

DECLARE @TSQL VARCHAR(8000), @VAR CHAR(2)
DECLARE @Prefixos VARCHAR(MAX);

WITH cte AS 
(
    SELECT DISTINCT prefixo 
    FROM (values ('1'),('2'),('3')) q(prefixo)
)
SELECT @Prefixos = COALESCE(@Prefixos + ', ', '') + prefixo 
FROM cte 
ORDER BY prefixo

SELECT  @TSQL = 'SELECT * FROM OPENQUERY(DICOI_LINKEDSERVER,''SELECT * FROM ssr.vw_sigas_diage where cd_prf_responsavel in ('''''  + @Prefixos + ''''''') order by cd_prf_responsavel, codigo'

select @TSQL as TSQL
-- EXEC (@TSQL)

Then you can visually check if there's something odd about it.
Or just try running that sql yourself and see if it fails or not.

From that T-SQL it returns this result :

SELECT * FROM OPENQUERY(DICOI_LINKEDSERVER,'SELECT * FROM ssr.vw_sigas_diage where cd_prf_responsavel in (''1, 2, 3''') order by cd_prf_responsavel, codigo

Notice that there's a bit to many single-quotes in that string. When using an IN with numbers, the single-quotes are not needed. And something is missing at the end.

...  in ('+ @Prefixos +') order by cd_prf_responsavel, codigo'');';