0

I'm using XML to create a dynamic SQL Query. I need to use the operator IN, but I can't make it work with multiples values.

To illustrate my matter here is what does work :

exec sp_executesql N'SELECT * FROM commercial_element WHERE specific_info IN (@IncludedListB)
                ',N'@IncludedListB nvarchar(30)',@IncludedListB='BAR'

The parameter @IncludedListB is built as a string in C#

filter.IncludedListB = 'BAR';

What I am trying to do is passing multiple paramters in the operator IN :

exec sp_executesql N'SELECT * FROM commercial_element WHERE specific_info IN (@IncludedListB)
                ',N'@IncludedListB nvarchar(30)',@IncludedListB='''BAR'',''BSO'''

Building like so :

filter.IncludedListB = ''BAR','BSO'';

Focusing only on the SQL Why does my second query doesn't work ? I'll figure it out for the C#.

Benoît
  • 143
  • 1
  • 2
  • 15
  • 3
    Because the `IN` operator expects a list of values, while you supply it with a single value that happens to contain a comma delimited string. – Zohar Peled Oct 16 '18 at 13:58
  • to elaborate on @ZoharPeled, it needs to be `IN ('val1' , 'val2')` instead of `IN('val1,val2')`. Notice the quotes. – S3S Oct 16 '18 at 13:59
  • @ZoharPeled My filter.IncludedListB should be a list of string ? And just pass it as a list in my query ? I tried and it didn't work. – Benoît Oct 16 '18 at 14:11
  • SQL Server does not have arrays or lists, you can pass a table valued parameter, or a comma delimited string and split it in sql, pass an xml or json and query it in sql, or simply pass multiple parameters, or you can construct the entire sql dynamically. IMHO, for large lists, a TVP is the best option. – Zohar Peled Oct 16 '18 at 14:14
  • Thanks for the information, I will use a slipt function – Benoît Oct 16 '18 at 14:15

0 Answers0