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?
CodePudding user response:
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.