SQL conversion failed when converting nvarchar value to data type int on SSRS

Question

I have the following query

SELECT * FROM A
WHERE Id IN (@Input)

where the @Input is a multi-value parameter from SSRS and Id is an integer on the database.

When I try to run it with a single value, it works; but when I try to use 2 values, such as '123,124', it always throws the error mentioned in the title.

I tried a bunch of different solution that showed up on Google, but none of them work at all, or it just returns an empty result (when I cast Id to varchar)

How can I make it work with the multi-value parameter?

Answer 1

Well, when passing the parameter values to the dataset, first you need to convert your input parameter values to the list of CSV values.

For example, if your parameter is like:

With properties such as:

You should consume that in your dataset using JOIN function.

e.g.:

=JOIN(Parameters!input.Value,",")

It should look like:

Now, your dataset will view/receive @input as '123,456,789'

And, now you can use any split function to split the values and can use them in your SQL.

In case if there is no split function available, you can use any custom SQL (an example given below) to change the multiple values to a single-valued table:

e.g.:

DECLARE @input VARCHAR(MAX) = '123,456,789'
IF OBJECT_ID('TEMPDB..#Multi') IS NOT NULL DROP TABLE #Multi;
CREATE TABLE #Multi (value  INT);
DECLARE @Insert VARCHAR(MAX) = 'INSERT INTO #Multi VALUES ('''+REPLACE(@input,',','''),(''')+''');';
EXEC (@Insert);
SELECT value FROM #Multi

Will return following output:

And you final SQL should be SQL chunk given above and:

SELECT * FROM A
WHERE Id IN (SELECT value FROM #Multi)

OR

SELECT * FROM A
JOIN #Multi M ON A.Id=M.value

Hope this help.