sql 存储过程参数为空则不作为条件
时间：2021-07-01 10:21:17
帮助过：7人阅读
***** Object: StoredProcedure [dbo].[GetCommonGroupByRegion] Script Date: 03/23/2017 17:31:18 ******/
SET ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER
ON
GO
ALTER 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
begin
DECLARE @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
解析： and (c.BuyTime<=@BuyTimeEnd or @BuyTimeEnd is null)
首先 and 条件后面是括号，那就说明括号 是一个整体， 括号里面 用的 or 语法，也就说明只要任意一个条件满足就ok, 当参数为空的时候，假如作为条件的字段是时间格式的，如果做大小比较肯定会失败，这个时候后面的 or的条件满足了，就把前面的给忽略了，其实这个时候就相当于 and 1=1 了，
sql 存储过程参数为空则不作为条件
标签：tco 存储过程 arc distinct 参数 roc arch har 比较
