sql 存储过程参数为空则不作为条件

/****** Object: StoredProcedure [dbo].[GetCommonGroupByRegion] Script Date: 03/23/2017 17:31:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER proc [dbo].[GetCommonGroupByRegion]
@CarCategory varchar(50)=null,---车型
@CreateTimeStart datetime=null, --入会开始时间
@CreateTimeEnd datetime=null, --入会结束时间
@AuthenticationTimeStart datetime=null, --认证开始时间
@AuthenticationTimeEnd datetime=null, --认证结束时间
@BuyTimeStart datetime=null, --购车开始时间
@BuyTimeEnd datetime=null --购车结束时间as
beginDECLARE @strPC VARCHAR(2000), @strAPP VARCHAR(2000), @strWX VARCHAR(2000), @strWAP VARCHAR(2000)
---------------------pc start
SET @strPC = ( SELECT DISTINCT STUFF(
( SELECT ','''+ qudao+'''' FROM CreatedType WITH ( NOLOCK )
WHERE qtype = 'pc' ORDER BY qudao
FOR XML PATH('')), 1,4, '') AS jzhw
FROM CreatedType t)
set @strPC= left(@strPC,len(@strPC)-1)
-------------------------pc end***********
------------------------app start
SET @strAPP = ( SELECT DISTINCT STUFF(
( SELECT ','''+ qudao+'''' FROM CreatedType WITH ( NOLOCK )
WHERE qtype = 'app' ORDER BY qudao
FOR XML PATH('')), 1,4, '') AS jzhw
FROM CreatedType t)
set @strAPP= left(@strAPP,len(@strAPP)-1)
------------------------- app end *******
------------------------wx start
SET @strWX = ( SELECT DISTINCT STUFF(
( SELECT ','''+ qudao+'''' FROM CreatedType WITH ( NOLOCK )
WHERE qtype = 'wx' ORDER BY qudao
FOR XML PATH('')), 1,4, '') AS jzhw
FROM CreatedType t)
set @strWX= left(@strWX,len(@strWX)-1)
------------------------- wx end *******
------------------------wap start
SET @strWAP = ( SELECT DISTINCT STUFF(
( SELECT ','''+ qudao+'''' FROM CreatedType WITH ( NOLOCK )
WHERE qtype = 'wap' ORDER BY qudao
FOR XML PATH('')), 1,4, '') AS jzhw
FROM CreatedType t)
set @strWAP= left(@strWAP,len(@strWAP)-1)
------------------------- wap end *******
select
c.CarCategory as '车型',
d.Region as '区域',
COUNT(case when a.CreatedPerson like '%D%' and len(a.CreatedPerson)=5 then a.Id else null end ) as '经销商',
COUNT(case when a.CreatedPerson in( @strPC) then a.Id else null end ) as '网站', COUNT(case when a.CreatedPerson in(@strAPP) then a.Id else null end ) as 'APP', COUNT(case when a.CreatedPerson in(@strWX) then a.Id else null end ) as '微信', COUNT(case when a.CreatedPerson in( @strWAP) then a.Id else null end ) as 'wap'
from
Membership a
left join IF_Customer b on a.IdentityNumber=b.IdentityNumber
left join IF_Car c on b.CustId=c.CustId
left join CS_CarDealerShip d on c.DealerId=d.DealerId
where
a.IsDel=0 --case when @CreateTimeStart is not null then and a.CreateTime>@CreateTimeStart else null end
--and ( (@CreateTimeStart is not null and @CreateTimeStart!='') and (a.CreateTime>@CreateTimeStart) )
--and ( (@CreateTimeEnd is not null and @CreateTimeEnd!='') and (a.CreateTime<=@CreateTimeEnd) ) and (a.CreateTime>=@CreateTimeStart or @CreateTimeStart is null)
and (a.CreateTime<=@CreateTimeEnd or @CreateTimeEnd is null) --and ( (@AuthenticationTimeStart is not null and @AuthenticationTimeStart!='') and (a.AuthenticationTime>@AuthenticationTimeStart) )
--and ( (@AuthenticationTimeEnd is not null and @AuthenticationTimeEnd!='') and (a.AuthenticationTime<=@AuthenticationTimeEnd) ) and (a.AuthenticationTime>=@AuthenticationTimeStart or @AuthenticationTimeStart is null)
and (a.AuthenticationTime<=@AuthenticationTimeEnd or @AuthenticationTimeEnd is null) --and ( (@CarCategory is not null and @CarCategory!='') and (c.CarCategory=@CarCategory) )
and (c.CarCategory=@CarCategory or @CarCategory is null ) --and ( (@BuyTimeStart is not null and @BuyTimeStart!='') and ( c.BuyTime>@BuyTimeStart) )
--and ( (@BuyTimeEnd is not null and @BuyTimeEnd!='') and ( c.BuyTime<=@BuyTimeEnd) ) and (c.BuyTime>=@BuyTimeStart or @BuyTimeStart is null)
and (c.BuyTime<=@BuyTimeEnd or @BuyTimeEnd is null) and d.Region<>''
and d.Region<>'-'
group by c.CarCategory, d.Region
; end