一个存储过程实现的效果如何用一句sql语句实现

[sql_help]

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语句来判断该如何实现?
谢谢

[dawugui]

你这个存在判断的话,就是你那么用.一条SQL实现不了.

[FlySQL]

一句足矣

select id,name from view_customer
where username=isnull(@UserName,username)

[dawugui]

或者你改为如下再试试:

select id,name from view_customer where customer_category in (select category from Custom_DataFilter where username=@UserName)
union all
select id,name from view_customer where customer_category not in (select category from Custom_DataFilter where username=@UserName)

[永生天地]

select id,name from view_customer where (customer_category
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)

[chuifengde]

SELECT distinct A.id,A.NAME FROM view_cusomer A  INNER JOIN (SELECT * FROM Custom_DataFilter WHERE username=@Username) B
ON A.customer_category=case WHEN exists(select 1 from AccessRule where name=@UserName) THEN 
B.category ELSE A.customer_category END