SSRS selecting results based on comma delimited list

Question

I have a table that stores IDs as INT, for example Table A:

|  ID  |  Name
   1       A
   2       B
   3       C

I have a query:

SELECT * FROM A WHERE ID IN (@ID)

This variable @ID will be coming from a comma separated list on the input parameter section in SSRS. The input will be something like 1,2,3

When I run this, I get the following error:

Conversion failed when converting the nvarchar value '1,2,3' to data type int.

Any thoughts?

Are you passing in '1,2,3' as a single string or are you passing a list of values from a multi-value parameter? If you are using a multi value parameter what you are trying to do should work, if you are passing a single string then you'll have to convert them to a table. There are plenty of examples of split functions for SQL Server 2008 if you do a quick search. — Alan Schofield, Sep 28 '17 at 09:29

score 3 · Accepted Answer · answered Sep 28 '17 at 09:49

3

As SSRS will return @ID as a comma-separated list probably the best way is search by charindex like this:

select * from A 
Where charindex(','+cast(id as varchar(max))+',' , ','+@ID+',',1)>0

answered Sep 28 '17 at 09:49

Jayvee

1 Answers1