CODEWITHSUNDEEP
sqlsql-server
JGV
JGV

Reputation: 5187

passing dynamically created comma separated values in a string to 'in' clause in SQL

I am trying to pass a comma separated values in a variable to the select statement but I am not getting the result.

Here is the SQL:

DECLARE @dataCodes XML = '<Root>
                    <List Value="120" />
                    <List Value="110" />
                </Root>';      

DECLARE @ConcatString VARCHAR(MAX);

SELECT @ConcatString = COALESCE(@ConcatString + ',','') + Code
FROM (
    SELECT T.Item.value('@Value[1]', 'VARCHAR(MAX)') AS Code
    FROM @dataCodes.nodes('/Root/List') AS T(Item)
    ) as TBL

SET @ConcatString=''''+REPLACE(@ConcatString,',',''',''') +'''';

SELECT NAME, ID, ROLE from MyTable where CODE in (@ConcatString); -- not working

Output of @ConcatString

enter image description here

Note:

If I pass the values directly as shown below, I am able to see the result,

SELECT NAME, ID, ROLE from MyTable where CODE in ('110','120');

I am trying this because, my SQL structure is something like:

 WITH JMATRIX_RESULT AS(      
       SELECT DISTINCT  ... from MyTable1 where CODE in (@ConcatString)
UNION
       SELECT DISTINCT  ... from MyTable2 where CODE in (@ConcatString)
UNION 
       SELECT DISTINCT  ... from MyTable3 where CODE in (@ConcatString)
),
...

and I see performance improvement when passing the parameter as comma separated values.

Expectation:

If I pass the dynamically created comma separated values in string (@ConcatString) to the in clause of select statement, it should return the values.

Any suggestion is appreciated.

Upvotes: 0

Views: 1292

Answers (1)

M.Ali
M.Ali

Reputation: 69494

Why are you concatenating the string simply do the following :

DECLARE @dataCodes XML = '<Root>
                            <List Value="120" />
                            <List Value="110" />
                          </Root>'; 


SELECT NAME, ID, ROLE 
from MyTable 
where CODE in (
                SELECT T.Item.value('@Value[1]', 'VARCHAR(MAX)') AS Code
                FROM @dataCodes.nodes('/Root/List') AS T(Item)
               );

Upvotes: 2

Related Questions