Skip to content
Advertisement

Checking if any value from a comma-separated list is selected in a multi-value parameter (in SQL dataset)

I have a field in my SQL dataset that contains a comma-separated list of short 2-4 letter codes — let’s call this CodeField. I also have a multi-value Report Parameter whose available values are the full set of possible codes — CodeParam. Basically, in my SQL query, I need to include a WHERE condition that includes rows where any of the codes in CodeField have been selected in CodeParam.

My initial approach was to generate a condition (either via custom code, or a JOIN in an SSRS expression) of the following form: ([CodeField] LIKE '%AA%') OR ([CodeField] LIKE '%BB%') OR .. (where AA, BB, .. are the values selected in CodeParam) and then include it as dynamic SQL as per this answer.

However, the comments there suggest that dynamically-generated SQL is usually the wrong way to go about things and that there’s probably a better way.

So, what’s the best way to solve this problem? Not overly concerned about performance of the query; I’m primarily looking for the simplest/cleanest effective solution.

Advertisement

Answer

So I’ll definitely mention that having a column that contains multiple data-points tends to not be ideal, for this exact reason.

Anyways, what I could recommend doing is something like the following:

  1. We assign every unique multi-value column a RowId.
  2. We split the column by delimiter (I used https://stackoverflow.com/a/28153222/1470896, but there are better ways to do this via stored procedure).
  3. We do a match using IN, and keep the distinct RowIds that match
  4. We filter the original results set by the matching RowIds

Here’s an example

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement