SSRS selecting results based on comma delimited li

问题:

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?

回答1:

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