Manipulate a string to be used in IN operator (SQL, SSRS)

I have an SSRS report with dataset that has query looking like this (simplified as the query is a lot more complex that this):

select * 
from myTable 
where country in (@Country)

@Country is a parameter and can have multiple values

It works fine, but we were asked to roll up all the values from one country to another one (the country is not in the list of parameters to be selected).

So for example we need to roll up all the records that belong to Canada to US if US was selected as one of the Countries essentially just replacing 'Canada' with 'US' in the dataset returned which is easy to achieve using REPLACE or even CASE statement. However, it gets a bit tricky with WHERE clause in the query. Does anyone know how to replace a string with another string so it's understood by IN operator?

If I simply do something like this:

select * 
from myTable 
where country in (replace(@Country, 'US', 'US,Canada'))

the query doesn't return anything and I understand the reasons behind it.

If I test this hardcoding the values like this:

select * 
from myTable 
where country in ('US', 'Canada')

it returns the correct rows of data. I think I'm looking for a way to join multiple values so it's understood properly by IN operator.

TIA, -TS.

Solution

You can use Dynamic SQL to achieve this. All you need to do is pass country name in declaration at first line.

Look at the code below:

DECLARE @SQL NVARCHAR(2000), @Country VARCHAR(30) = 'Canada'

SELECT @SQL = CONCAT('
    select 
        * 
    from myTable 
    where country in (', 
    CASE WHEN @Country = 'US' THEN '''US'', ''Canada''' else '@Country' end , ')'
)

EXEC sp_executesql @SQL, N'@Country VARCHAR(30)', @Country = @Country

Here's a fiddle : http://sqlfiddle.com/#!18/b7f49/4