下面是我写的存储过程,结果是0 ,但是我直接拿出来写成普通的sql语句就有值
想问问各位大神应该怎么写
alter proc [dbo].[P_GetNumByCurrency_New]
@BPConstructionProject_ID nvarchar(max),
@BeginTime nvarchar(60) ,
@EndTime nvarchar(60) ,
@SellProductMarkNo nvarchar(max) ,
@PouringPlace nvarchar(max)
AS
BEGIN
declare @qxtable table(ID uniqueidentifier)
insert into @qxtable
SELECT
distinct
p1.BPConstructionProject_ID
FROM BPConstructionProjectBCSite as p1
inner join BOSplit(@BPConstructionProject_ID,';') pp5 on p1.BCSite_ID=pp5.value
if (len(@SellProductMarkNo)=0 and len(@PouringPlace)=0)
begin
SELECT
BOContract_Num=(
select ISNULL(COUNT(*),0)
from(
select distinct ID from(
select distinct p1.ID FROM BOContract AS P1
inner join BOOrder p2 on P1.ID=p2.BOContract_ID
inner join BPConstructionProjectBCSite q4 on p1.BPConstructionProjectBCSite_ID=q4.id
inner JOIN BPConstructionProject Q5 ON Q4.BPConstructionProject_ID=Q5.ID
inner join @qxtable Q8 on Q5.ID=Q8.ID
inner join BOContractStatus as p4 on P1.BOContractStatus_ID=p4.ID
WHERE 1=1
and cast(LEFT(cast(p4.Sequence as nvarchar(3)),1) as int)>=1
and cast(LEFT(cast(p4.Sequence as nvarchar(3)),1) as int)<=4
and p2.SetTime>=@BeginTime
and p2.SetTime<=@EndTime
and p1.IsDelete=0
and p2.IsDelete=0
)A
)B
),
BOOrder_Num=(
select isnull(COUNT(p4.ID),0) from BOContract p1
inner join BOOrder p4 on p1.ID=p4.BOContract_ID
inner join BPConstructionProjectBCSite p2 on p1.BPConstructionProjectBCSite_ID=p2.ID
inner join @qxtable p5 on p2.BPConstructionProject_ID=p5.ID
where 1=1
and p4.SetTime>=@BeginTime
and p4.SetTime<=@EndTime
and p1.IsDelete=0
and p4.IsDelete=0
)
end
-------------------------------------------
BOContract_Num BOOrder_Num
0 0
-------------------------------------------图片select与上面的写法是一样的
想问问各位大神应该怎么写
alter proc [dbo].[P_GetNumByCurrency_New]
@BPConstructionProject_ID nvarchar(max),
@BeginTime nvarchar(60) ,
@EndTime nvarchar(60) ,
@SellProductMarkNo nvarchar(max) ,
@PouringPlace nvarchar(max)
AS
BEGIN
declare @qxtable table(ID uniqueidentifier)
insert into @qxtable
SELECT
distinct
p1.BPConstructionProject_ID
FROM BPConstructionProjectBCSite as p1
inner join BOSplit(@BPConstructionProject_ID,';') pp5 on p1.BCSite_ID=pp5.value
if (len(@SellProductMarkNo)=0 and len(@PouringPlace)=0)
begin
SELECT
BOContract_Num=(
select ISNULL(COUNT(*),0)
from(
select distinct ID from(
select distinct p1.ID FROM BOContract AS P1
inner join BOOrder p2 on P1.ID=p2.BOContract_ID
inner join BPConstructionProjectBCSite q4 on p1.BPConstructionProjectBCSite_ID=q4.id
inner JOIN BPConstructionProject Q5 ON Q4.BPConstructionProject_ID=Q5.ID
inner join @qxtable Q8 on Q5.ID=Q8.ID
inner join BOContractStatus as p4 on P1.BOContractStatus_ID=p4.ID
WHERE 1=1
and cast(LEFT(cast(p4.Sequence as nvarchar(3)),1) as int)>=1
and cast(LEFT(cast(p4.Sequence as nvarchar(3)),1) as int)<=4
and p2.SetTime>=@BeginTime
and p2.SetTime<=@EndTime
and p1.IsDelete=0
and p2.IsDelete=0
)A
)B
),
BOOrder_Num=(
select isnull(COUNT(p4.ID),0) from BOContract p1
inner join BOOrder p4 on p1.ID=p4.BOContract_ID
inner join BPConstructionProjectBCSite p2 on p1.BPConstructionProjectBCSite_ID=p2.ID
inner join @qxtable p5 on p2.BPConstructionProject_ID=p5.ID
where 1=1
and p4.SetTime>=@BeginTime
and p4.SetTime<=@EndTime
and p1.IsDelete=0
and p4.IsDelete=0
)
end
-------------------------------------------
BOContract_Num BOOrder_Num
0 0
-------------------------------------------图片select与上面的写法是一样的
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货