CREATE PROCEDURE queryjxchuizong @bianhao varchar(20) , @cangku varchar(30) ,@supplyman varchar(30), @instorebegin varchar(20), @instoreend varchar(20) , @outstorebegin varchar(20) ,@outstoreend varchar(20)
AS
if(@cangku = "all" )begin
select fgoods_id,sum(fnum+salenum) as innum,sum(fnum*inprice+salenum*inprice) AS INMONEY,0 AS OUTNUM,0 AS OUTMONEY,0 AS mixnumber,0 AS mixmoney into #DDD
from t_stock_detail where (convert(VARCHAR,indate,121) BETWEEN '2008-11-01' AND '2008-11-20') group by fgoods_id
union
select fgoods_id,0 as innum,0 AS INMONEY,sum(salenum) AS OUTNUM,sum(salenum*saleprice) AS OUTMONEY ,0 AS mixnumber,0 AS mixmoney
from t_stock_detail where and (convert(VARCHAR,SALEDATE,121) BETWEEN '2008-11-01' AND '2008-11-20') group by fgoods_id
union
select t2.fgoods_id,0 as innum,0 AS inmoney,0 AS outmoney,0 AS mixnumber,sum(t2.fIc_amount) as mixnumber,sum(t2.fIc_amount*t2.fprice) as mixmoney from t_ic_master t1, t_ic_detail t2
where (convert(varchar,t1.fIc_date,120) between '2008-11-01' and '2008-11-20') and t2.fIc_no=t1.fIc_code and t2.fFlag='Y' and t1.fFlag='Y'
group by fgoods_id else if(@cangku != "all")
begin
select fgoods_id,sum(fnum+salenum) as innum,sum(fnum*inprice+salenum*inprice) AS INMONEY,0 AS OUTNUM,0 AS OUTMONEY,0 AS mixnumber,0 AS mixmoney into #DDD
from t_stock_detail where fstock_id=@cangku and (convert(VARCHAR,indate,121) BETWEEN '2008-11-01' AND '2008-11-20') group by fgoods_id
union
select fgoods_id,0 as innum,0 AS INMONEY,sum(salenum) AS OUTNUM,sum(salenum*saleprice) AS OUTMONEY ,0 AS mixnumber,0 AS mixmoney
from t_stock_detail where fstock_id=@cangku and (convert(VARCHAR,SALEDATE,121) BETWEEN '2008-11-01' AND '2008-11-20') group by fgoods_id
union
select t2.fgoods_id,0 as innum,0 AS inmoney,0 AS outmoney,0 AS mixnumber,sum(t2.fIc_amount) as mixnumber,sum(t2.fIc_amount*t2.fprice) as mixmoney from t_ic_master t1, t_ic_detail t2
where (convert(varchar,t1.fIc_date,120) between '2008-11-01' and '2008-11-20') and t2.fIc_no=t1.fIc_code and t2.fFlag='Y' and t1.fFlag='Y'
group by fgoods_id
endselect * from #DDD group by fgoods_id高手帮我看下存储过程的问题吧
AS
set nocount on
if(@cangku = "all" ) begin
select fgoods_id,sum(fnum+salenum) as innum,sum(fnum*inprice+salenum*inprice) AS INMONEY,0 AS OUTNUM,0 AS OUTMONEY,0 AS mixnumber,0 AS mixmoney into #DDD
from t_stock_detail where (convert(VARCHAR,indate,121) BETWEEN '2008-11-01' AND '2008-11-20') group by fgoods_id
union
select fgoods_id,0 as innum,0 AS INMONEY,sum(salenum) AS OUTNUM,sum(salenum*saleprice) AS OUTMONEY ,0 AS mixnumber,0 AS mixmoney
from t_stock_detail where --and
(convert(VARCHAR,SALEDATE,121) BETWEEN '2008-11-01' AND '2008-11-20') group by fgoods_id
union
select t2.fgoods_id,0 as innum,0 AS inmoney,0 AS outmoney,0 AS mixnumber,sum(t2.fIc_amount) as mixnumber,sum(t2.fIc_amount*t2.fprice) as mixmoney from t_ic_master t1, t_ic_detail t2
where (convert(varchar,t1.fIc_date,120) between '2008-11-01' and '2008-11-20') and t2.fIc_no=t1.fIc_code and t2.fFlag='Y' and t1.fFlag='Y'
group by fgoods_id
end
else if(@cangku != "all")
begin
select fgoods_id,sum(fnum+salenum) as innum,sum(fnum*inprice+salenum*inprice) AS INMONEY,0 AS OUTNUM,0 AS OUTMONEY,0 AS mixnumber,0 AS mixmoney
into #DDD1
from t_stock_detail where fstock_id=@cangku and (convert(VARCHAR,indate,121) BETWEEN '2008-11-01' AND '2008-11-20') group by fgoods_id
union
select fgoods_id,0 as innum,0 AS INMONEY,sum(salenum) AS OUTNUM,sum(salenum*saleprice) AS OUTMONEY ,0 AS mixnumber,0 AS mixmoney
from t_stock_detail where fstock_id=@cangku and (convert(VARCHAR,SALEDATE,121) BETWEEN '2008-11-01' AND '2008-11-20') group by fgoods_id
union
select t2.fgoods_id,0 as innum,0 AS inmoney,0 AS outmoney,0 AS mixnumber,sum(t2.fIc_amount) as mixnumber,sum(t2.fIc_amount*t2.fprice) as mixmoney from t_ic_master t1, t_ic_detail t2
where (convert(varchar,t1.fIc_date,120) between '2008-11-01' and '2008-11-20') and t2.fIc_no=t1.fIc_code and t2.fFlag='Y' and t1.fFlag='Y'
group by fgoods_id
end
2.第二个错误 where 后面多一个 and ,去掉就好了
3.临时表#DDD被用两次,已经把第二次改为#DDD1
@bianhao varchar(20)
,@cangku varchar(30)
,@supplyman varchar(30)
,@instorebegin varchar(20)
,@instoreend varchar(20)
,@outstorebegin varchar(20)
,@outstoreend varchar(20)
AS
if(@cangku = "all" ) begin
select fgoods_id,sum(fnum+salenum) as innum,sum(fnum*inprice+salenum*inprice) AS INMONEY,0 AS OUTNUM,0 AS OUTMONEY,0 AS mixnumber,0 AS mixmoney into #DDD
from t_stock_detail
where (convert(VARCHAR,indate,121) BETWEEN '2008-11-01' AND '2008-11-20')
group by fgoods_id
union
select fgoods_id,0 as innum,0 AS INMONEY,sum(salenum) AS OUTNUM,sum(salenum*saleprice) AS OUTMONEY ,0 AS mixnumber,0 AS mixmoney
from t_stock_detail
where --and 多出了一个
(convert(VARCHAR,SALEDATE,121) BETWEEN '2008-11-01' AND '2008-11-20')
group by fgoods_id
union
select t2.fgoods_id,0 as innum,0 AS inmoney,0 AS outmoney,0 AS mixnumber,sum(t2.fIc_amount) as mixnumber,sum(t2.fIc_amount*t2.fprice) as mixmoney from t_ic_master t1, t_ic_detail t2
where (convert(varchar,t1.fIc_date,120) between '2008-11-01' and '2008-11-20') and t2.fIc_no=t1.fIc_code and t2.fFlag='Y' and t1.fFlag='Y'
group by fgoods_id
END --少了end
else if(@cangku != "all")
begin
select fgoods_id,sum(fnum+salenum) as innum,sum(fnum*inprice+salenum*inprice) AS INMONEY,0 AS OUTNUM,0 AS OUTMONEY,0 AS mixnumber,0 AS mixmoney into #EEE --重复使用了临时表
from t_stock_detail
where fstock_id=@cangku and (convert(VARCHAR,indate,121) BETWEEN '2008-11-01' AND '2008-11-20')
group by fgoods_id
union
select fgoods_id,0 as innum,0 AS INMONEY,sum(salenum) AS OUTNUM,sum(salenum*saleprice) AS OUTMONEY ,0 AS mixnumber,0 AS mixmoney
from t_stock_detail
where fstock_id=@cangku and (convert(VARCHAR,SALEDATE,121) BETWEEN '2008-11-01' AND '2008-11-20')
group by fgoods_id
union
select t2.fgoods_id,0 as innum,0 AS inmoney,0 AS outmoney,0 AS mixnumber,sum(t2.fIc_amount) as mixnumber,sum(t2.fIc_amount*t2.fprice) as mixmoney from t_ic_master t1, t_ic_detail t2
where (convert(varchar,t1.fIc_date,120) between '2008-11-01' and '2008-11-20') and t2.fIc_no=t1.fIc_code and t2.fFlag='Y' and t1.fFlag='Y'
group by fgoods_id
end select * from #DDD group by fgoods_id select * from #EEE group by fgoods_id