T-SQL Where clause with nvarchar composed by a string_agg in openquery

Question

I'm creating a stored procedure where I have this openquery:

SELECT @MY_QUERY = 'SELECT * FROM OPENQUERY(HYPER_CONN, ''
    SELECT COALESCE(SUM(QUANT_RIGA),0) FROM DDT_CLI_RIGHE WHERE DOC_RIGA_ID = ''''' + @THE_DDT + ''''' '')'

In the where clause I set this variable @THEDDT:

DECLARE @THE_DDT nvarchar(MAX) = (SELECT STRING_AGG(DOC_RIGA_ID,',') FROM ...

For example it will be like @THEDDT = test1,test2,test3

I want set this variable in my "where in" clause.

So in the openquery I'm trying to have something like this (but using the varaible):

WHERE DOC_RIGA_ID IN ('test1','test2','test3') 

Is there a way to do this?

Answer 1

This is more of a stab in the dark, as we don't have all the information, however, as I mentioned, it seems all you need to do is change your DOC_RIGA_ID = {literal} clause to a DOC_RIGA_ID IN ({delimited List}) clause:

DECLARE @THE_DDT nvarchar(MAX) = (SELECT STRING_AGG(QUOTENAME(DOC_RIGA_ID,''''),',') --Assumes DOC_RIGA_ID can't have a value longer than 128 characters
                                  FROM ...

SELECT @MY_QUERY = 'SELECT * FROM OPENQUERY(HYPER_CONN, ''
    SELECT COALESCE(SUM(QUANT_RIGA),0) FROM DDT_CLI_RIGHE WHERE DOC_RIGA_ID IN (' + REPLACE(@THE_DDT,'''','''''') + ')'') OQ;';

This is, of course, untested as I have no way of testing this statement.