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

Question

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

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.

what is the data type of code

wiretext
– wiretext

2015-10-01 17:44:03 +00:00
Commented Oct 1, 2015 at 17:44 — wiretext
– wiretext, Commented Oct 1, 2015 at 17:44
datatype of code is VARCHAR

JGV
– JGV

2015-10-01 17:46:16 +00:00
Commented Oct 1, 2015 at 17:46 — JGV
– JGV, Commented Oct 1, 2015 at 17:46
you have to create dynamic query then execute it

wiretext
– wiretext

2015-10-01 17:50:03 +00:00
Commented Oct 1, 2015 at 17:50 — wiretext
– wiretext, Commented Oct 1, 2015 at 17:50

M.Ali · Accepted Answer · 2015-10-01 18:00:14Z

2

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)
               );

answered Oct 1, 2015 at 18:00

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JGV Over a year ago

My original code was like this but, I am having performance issue and I observed that replacing the current select statement within the 'in' clause with comma separated string increased the performance significantly. I have updated the question with my SQL Structure and I hope it gives the idea.

JGV Over a year ago

I am staying with the same code, which is the solution you have suggested.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related