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.

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

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp

SELECT
    *
    , ROW_NUMBER() OVER (ORDER BY NEWID()) AS RowId
INTO #Temp
FROM (VALUES ('abc,def,ghi,jkl'), ('mno,pqr,stu,vwx')) AS MyTable(MyColumn);

WITH StringSplitWithRowId AS (
    SELECT
        RowId
        , LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value'
    FROM (
        SELECT
            RowId
            , CAST('<M>' + REPLACE( MyColumn, ',', '</M><M>') + '</M>' AS XML) AS Data
        FROM #Temp
    ) a
    CROSS APPLY Data.nodes ('/M') AS Split(a)
),

RowIdsToInclude AS (
SELECT DISTINCT
    RowId
FROM StringSplitWithRowId
WHERE Value IN (@SsrsReportParameter) -- query will not work in ssms, needs to be run in RS
)

SELECT
    *
FROM #Temp t
INNER JOIN RowIdsToInclude i
ON t.RowId = i.RowId