-1

A Parameter returns a list of various numeric values:

[12345, 1234, 123]

The length and quantity vary, e.g. it could be a list of 100 or a list of only 5 values.

Now I want to use those values in the WHERE of a Microsoft SQL-Server query. I thought of using IN. Therefore I need to convert those values into the right syntax:

('12345', '1234', '123')

Some ideas for the right conversion?

Other better(easier) solutions without using LIKE?

Edit: Sorry. My case description wasn't clear at all. The SQL Query is inside iReport and the Parameter (e.g. $P{DATUM_BIS}) is returned by external software.

Alex K
  • 22,315
  • 19
  • 108
  • 236
JanNatter
  • 1
  • 1
  • 1
    You can't use `LIKE`. Use `IN`. – Sergey Kalinichenko May 02 '17 at 12:09
  • `where f in (a, b, c, d ...)` is the closest form but you would need to protect against injection if there is any possibility of non-numeric values. Looping the string, inserting into a temp table/table variable and joining or using a sub-query is another option. – Alex K. May 02 '17 at 12:09
  • 3
    Soooo many duplicates. Please do some research before asking. Here's one: http://stackoverflow.com/questions/512105/passing-multiple-values-for-a-single-parameter-in-reporting-services – Tab Alleman May 02 '17 at 12:10
  • Possible duplicate of [Parameterize an SQL IN clause](http://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) – Sean Lange May 02 '17 at 13:20
  • @JanNatter You should use `$X{}` syntax. [JasperReports - Query Sample](http://jasperreports.sourceforge.net/sample.reference/query/index.html#query) – Alex K May 02 '17 at 15:43
  • @AlexK Thank you. `$X{IN, , } `worked perfect for me – JanNatter May 03 '17 at 11:32
  • You are welcome :) – Alex K May 03 '17 at 11:37

1 Answers1

0

In general, you should not approach this by munging strings. If you are generating the list of values in the database, then you want to do all the work in the database -- and not put the values in a parameter.

But, if you are stuck with this method for some reason, you can use like:

where replace(replace(@param, '[', ', '), ']', ', ')) like '%, ' + cast(col as varchar(255)) + ', %'

This will not make use of indexes, but it does implement the logic that you want as a (relatively) simple query.

I should note that SQL Server 2016 introduces string_split() which offers other options with better performance.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786