SQL存储过程执行出错~~

[COMPUTERZZZ]

declare @begindate varchar(10)
set @begindate='2007-05-01'

delete yj where convert(char(7),yjdate,120)=left(@begindate,7)
--复制上月月结数据
insert into yj(yjdate,bomid,qty_a_l,amount_a_l,in_qtys,amount_m,out_qtys,aunitprice,amount_o,
qty_a,amount_a)
select @begindate,bomid,qty_a,amount_a,in_qtys,amount_m,out_qtys,aunitprice,amount_o,
qty_a,amount_a from yj where convert(char(7),yjdate,120)=
left(convert(char(7),dateadd(month,-1,convert(datetime,@begindate,120)),120),7)
--对COPY的数据进行修改
--更新 入,出库数,入库金额
update yj set in_qtys=b.in_qty,out_qtys=b.out_qty,amount_m=b.amount_m
from yj inner join (
select bomid,isnull(sum(in_qty),0) in_qty,
isnull(sum(amount),0) amount_m,isnull(sum(out_qty),0) out_qty
from delivery_view where convert(char(7),datetime,120)=left(@begindate,7) group by bomid ) b
on yj.bomid=b.bomid
where convert(char(7),yj.yjdate,120)=left(@begindate,7)


--当上月结存数为0(即本月期初数为0)并且本月入库也为0时,平均单价为0
update yj set aunitprice=0, --平均单价
amount_o=out_qtys*0, --本月出库金额
qty_a=qty_a_l+in_qtys-out_qtys, --结存数
amount_a=amount_a_l+amount_m-(out_qtys*0), --结存金额
where yj.qty_a_l=0 and yj.in_qtys=0 and convert(char(7),yjdate,120)=left(@begindate,7)

--当上月结存数不为(即本月期初数量不为0)0或本月入库数不为0时
--这一段出错,提示:
服务器: 消息 8134，级别 16，状态 1，行 31
Divide by zero error encountered.
The statement has been terminated.
被0除.可是这个我已经把为0的记录去除了.而且我把它COPY到一个新查询中运行是没问题的.很奇怪!!
update yj set aunitprice=(amount_a_l+amount_m)/(qty_a_l+in_qtys), --平均单价
amount_o=out_qtys*convert(numeric(18,2),(amount_a_l+amount_m)/(qty_a_l+in_qtys)), --本月出库金额
qty_a=qty_a_l+in_qtys-out_qtys, --结存数
amount_a=amount_a_l+amount_m-
(out_qtys*convert(numeric(18,2),(amount_a_l+amount_m)/(qty_a_l+in_qtys))) --结存金额
where (qty_a_l<>0 or in_qtys>0) and convert(char(7),yjdate,120)=left(@begindate,7)

--上月月结中的物料在本月无出入记录
update yj set in_qtys=0,amount_m=0,out_qtys=0,
aunitprice=0, --平均单价
amount_o=0, --本月出库金额
qty_a=qty_a_l, --结存数
amount_a=amount_a_l --结存金额
where convert(char(7),yjdate,120)=
left(@begindate,7) and yj.bomid not in
(
select bomid from delivery_view where convert(char(7),datetime,120)=convert(char(7),@begindate,120)
)

----处理yj中不存在的物料-----
--当入库数据量>0时
insert into yj(yjdate,bomid,qty_a_l,amount_a_l,in_qtys,amount_m,out_qtys,aunitprice,amount_o,
qty_a,amount_a)
select convert(datetime,@begindate,120),bomid,0 qty_a_l,0 amount_a_l,
sum(in_qty) in_qty,sum(amount) amount,sum(out_qty) out_qty,
convert(numeric(18,2),sum(amount)/sum(in_qty)) aunitprice, --平均单价
sum(out_qty)*convert(numeric(18,2),sum(amount)/sum(in_qty)) as amount_o,
sum(in_qty)-sum(out_qty) qty_a,
sum(amount)-sum(out_qty)*convert(numeric(18,2),sum(amount)/sum(in_qty)) amount_a
from (
select * from delivery_view where bomid not in
( select bomid from yj where convert(char(7),yjdate,120)=convert(char(7),@begindate,120))
and convert(char(7),datetime,120)=convert(char(7),@begindate,120)
) a
group by bomid
having sum(in_qty)>0

--当入库数量=0时,平均单价按0计算
insert into yj(yjdate,bomid,qty_a_l,amount_a_l,in_qtys,amount_m,out_qtys,aunitprice,amount_o,
qty_a,amount_a)
select convert(datetime,@begindate,120),bomid,0 qty_a_l,0 amount_a_l,
sum(in_qty) in_qty,sum(amount) amount,
sum(out_qty) out_qty,0 aunitprice, --平均单价
sum(out_qty)*0 as amount_o,
sum(in_qty)-sum(out_qty) qty_a,
sum(amount)-sum(out_qty)*0 amount_a
from (
select * from delivery_view where bomid not in
( select bomid from yj where convert(char(7),yjdate,120)=convert(char(7),@begindate,120) )
and convert(char(7),datetime,120)=convert(char(7),@begindate,120)
) a
group by bomid
having sum(in_qty)=0

[肥胖的柠檬]

update yj set aunitprice=0, --平均单价
amount_o=out_qtys*0, --本月出库金额
qty_a=qty_a_l+in_qtys-out_qtys, --结存数
amount_a=amount_a_l+amount_m-(out_qtys*0), --结存金额
where yj.qty_a_l=0 and yj.in_qtys=0 and convert(char(7),yjdate,120)=left(@begindate,7)

--当上月结存数不为(即本月期初数量不为0)0或本月入库数不为0时


amount_a=amount_a_l+amount_m-(out_qtys*0), --结存金额
这里多了个,号

不过LZ想说的是执行出错,是什么错呢?

[lwl0606]

--当上月结存数不为(即本月期初数量不为0)0或本月入库数不为0时
--这一段出错,提示:
update yj set aunitprice=( case when qty_a_l+in_qtys=0 then 0 else (amount_a_l+amount_m)/(qty_a_l+in_qtys) end ), --平均单价
amount_o=out_qtys*convert(numeric(18,2),( case when qty_a_l+in_qtys=0 then 0 else (amount_a_l+amount_m)/(qty_a_l+in_qtys) end )), --本月出库金额
qty_a=qty_a_l+in_qtys-out_qtys, --结存数
amount_a=amount_a_l+amount_m-
(out_qtys*convert(numeric(18,2),( case when qty_a_l+in_qtys=0 then 0 else (amount_a_l+amount_m)/(qty_a_l+in_qtys) end))) --结存金额
where (qty_a_l<>0 or in_qtys>0) and convert(char(7),yjdate,120)=left(@begindate,7)


这样试试看行不行

[COMPUTERZZZ]

但是我单独把它COPY出来,到一个新查询中执行却没问题!是为什么呢?

[lwl0606]

你测试的数据何存储过程里面的数据一样的嘛？
应该是表里面有的数据 会使 分母 为 0 ，你可能没有测试，如果分母不是0 ，那段代码就没问题

[zhj92lxs]

看看

[COMPUTERZZZ]

就是这个问题分母为0,可是我单独把它COPY出来,到一个新查询中执行却没问题!是为什么呢?

[COMPUTERZZZ]

我已经能过条件去掉了分母为0的可能(where yj.qty_a_l=0 and yj.in_qtys=0 and convert(char(7),yjdate,120)=left(@begindate,7) )
可是它还是提示:被0除.

~~~

[lwl0606]

上面粘错了吧，应该是
where (qty_a_l<>0 or in_qtys>0) and convert(char(7),yjdate,120)=left(@begindate,7)
吧
，你qty_a_l 和 in_qtys 会不会有 负值， （－1）＋1 ＝0 ，
先用 select 把你的数据查出来看一下，因该还是数据的问题