SSRS中多值参数处理

1. @JoinID=Join(Parameters!Pn_id.Value,",")

将多值参数的值用逗号连接成一个字符串，

( SELECT '''+REPLACE(@pn_id,',',''' pid UNION ALL SELECT ''')+''' pid) 

将字符串通过replace，uniion转换成一张表

注意： union 和union all 区别， union无重复行，union all 不剔除重复行

将字符串转换成一张表后再进行查找就方便了,下面这个例子就是将字符串转换为c表后，找出在c表而不在a表中的记录，并给出一个Not Exists的记录

select c.pid,''Not Exists'' as Pstate from( SELECT '''+REPLACE(@JoinID,',',''' pid UNION ALL SELECT ''')+''' pid ) c where c.pid not in
(SELECT pn_id FROM pen_link_component a WITH (nolock))'

 

2. 还有另一种方法将字符串分割并转换成一张表

declare @tb table(b varchar(max))

insert into @tb select @JoinID b ;
select c.split,'Not Exists' as Pstate from (
select SUBSTRING(t.b, number ,CHARINDEX(',',t.b+',',number)-number) as split
from @tb t,master..spt_values s where s.number >=1 and s.type = 'P' and SUBSTRING(','+t.b,s.number,1) = ',' ) c where c.split not in
(SELECT pn_id FROM pen_link_component WITH (nolock))

利用charindex函数和master..spt_values表中的number来将进行分割