create proc [dbo].[sp_filter_customer]
(
@UserName nvarchar(256)
)
as
begin if exists(select 1 from AccessRule where name=@UserName)
select id,name from view_customer
where customer_category
in
(
select category from Custom_DataFilter
where username=@UserName
)
else
select id,name from view_customer
end
存储过程的方式如下,主要就是先判断AccessRule中有没有该用户的数据,如果有使用数据过滤,没有就读取所有的数据?
现在如果要用一句sql语句来判断该如何实现?
谢谢
where username=isnull(@UserName,username)
union all
select id,name from view_customer where customer_category not in (select category from Custom_DataFilter where username=@UserName)
in
(
select category from Custom_DataFilter
where username=@UserName
) and exists(select 1 from AccessRule where name=@UserName))
or not exists(select 1 from AccessRule where name=@UserName)
ON A.customer_category=case WHEN exists(select 1 from AccessRule where name=@UserName) THEN
B.category ELSE A.customer_category END