应该在每个语句后判断状态CREATE PROCEDURE Sp_Mychuku
(@gdh varchar(20),@czy varchar(20))
AS
BEGIN TRANSACTION
--删除临时表中不合格的数据,其中包括已经出库数据及未注明的数据
delete from Stuff_OutLs where kfys=0 or ckbz=1
if @@error <> 0 goto err_handle
--将合格数据插入到出库表中
Insert Into Stuff_Out (ckrq,gdh,StuffID,dlxm,kf,kgy,llr,spdm,hw,jldw,ckds,mdyz,cksl,cbje,cbdj,jjje,
jjdj,zydds,zydsl,sjckds,sjcksl,sjcbje,sjjjje,czy) SELECT ckrq,gdh,StuffID,dlxm,kf,kgy,llr,spdm,hw,
jldw,ckds,mdyz,cksl,cbje,cbdj,jjje,jjdj,zydds,zydsl,ckds,cksl,cbje,jjje,czy FROM Stuff_OutLs if @@error <> 0 goto err_handle --修改库存数据,应该是将临时表的材料代码及库房号相同的数据汇总写入库存数据中
--如果超库存数据(包括库存数量、成本金额、进价金额),则显示错误,且退出
update a set a.kcsl = a.kcsl-b.cksl,a.cbje=a.cbje -b.cbje,a.jjje=a.jjje-b.jjje,
a.cbdj=round((a.cbje-b.cbje)/(a.kcsl-b.cksl),6),a.jjdj=round((a.jjje-b.jjje)/(a.kcsl-b.cksl),6)
from Stuff_Actual a inner join
(select spdm,kf,sum(cksl) as cksl,sum(cbje) as cbje,sum(jjje) as jjje from Stuff_OutLs group by spdm,kf)b
on a.spdm=b.spdm and a.kf=b.kf if @@error <> 0 goto err_handle --修改大料表中的数据
update a set a.ckds=a.ckds+b.ckds,a.cksl=a.cksl+b.cksl from Work_Stuff a left join Stuff_OutLs b
on a.id= b.Stuffid where a.id=b.Stuffid and a.gdh=@gdh and b.czy=@czy and b.kfys=1 and b.ckbz=0 if @@error <> 0 goto err_handle --删除临时表的数据
delete from Stuff_OutLs if @@error = 0 COMMIT TRANSACTION
return
err_handle :
print '出错误了,不能出库'
rollback TRANSACTION
GO
(@gdh varchar(20),@czy varchar(20))
AS
BEGIN TRANSACTION
--删除临时表中不合格的数据,其中包括已经出库数据及未注明的数据
delete from Stuff_OutLs where kfys=0 or ckbz=1
if @@error <> 0 goto err_handle
--将合格数据插入到出库表中
Insert Into Stuff_Out (ckrq,gdh,StuffID,dlxm,kf,kgy,llr,spdm,hw,jldw,ckds,mdyz,cksl,cbje,cbdj,jjje,
jjdj,zydds,zydsl,sjckds,sjcksl,sjcbje,sjjjje,czy) SELECT ckrq,gdh,StuffID,dlxm,kf,kgy,llr,spdm,hw,
jldw,ckds,mdyz,cksl,cbje,cbdj,jjje,jjdj,zydds,zydsl,ckds,cksl,cbje,jjje,czy FROM Stuff_OutLs if @@error <> 0 goto err_handle --修改库存数据,应该是将临时表的材料代码及库房号相同的数据汇总写入库存数据中
--如果超库存数据(包括库存数量、成本金额、进价金额),则显示错误,且退出
update a set a.kcsl = a.kcsl-b.cksl,a.cbje=a.cbje -b.cbje,a.jjje=a.jjje-b.jjje,
a.cbdj=round((a.cbje-b.cbje)/(a.kcsl-b.cksl),6),a.jjdj=round((a.jjje-b.jjje)/(a.kcsl-b.cksl),6)
from Stuff_Actual a inner join
(select spdm,kf,sum(cksl) as cksl,sum(cbje) as cbje,sum(jjje) as jjje from Stuff_OutLs group by spdm,kf)b
on a.spdm=b.spdm and a.kf=b.kf if @@error <> 0 goto err_handle --修改大料表中的数据
update a set a.ckds=a.ckds+b.ckds,a.cksl=a.cksl+b.cksl from Work_Stuff a left join Stuff_OutLs b
on a.id= b.Stuffid where a.id=b.Stuffid and a.gdh=@gdh and b.czy=@czy and b.kfys=1 and b.ckbz=0 if @@error <> 0 goto err_handle --删除临时表的数据
delete from Stuff_OutLs if @@error = 0 COMMIT TRANSACTION
return
err_handle :
print '出错误了,不能出库'
rollback TRANSACTION
GO
CREATE PROCEDURE Sp_Mychuku
(@gdh varchar(20),@czy varchar(20))
AS
BEGIN TRANSACTION
--删除临时表中不合格的数据,其中包括已经出库数据及未注明的数据
delete from Stuff_OutLs where kfys=0 or ckbz=1
if @@error <> 0 goto Error
--将合格数据插入到出库表中
Insert Into Stuff_Out (ckrq,gdh,StuffID,dlxm,kf,kgy,llr,spdm,hw,jldw,ckds,mdyz,cksl,cbje,cbdj,jjje,
jjdj,zydds,zydsl,sjckds,sjcksl,sjcbje,sjjjje,czy) SELECT ckrq,gdh,StuffID,dlxm,kf,kgy,llr,spdm,hw,
jldw,ckds,mdyz,cksl,cbje,cbdj,jjje,jjdj,zydds,zydsl,ckds,cksl,cbje,jjje,czy FROM Stuff_OutLs
if @@error <> 0 goto Error--修改库存数据,应该是将临时表的材料代码及库房号相同的数据汇总写入库存数据中
--如果超库存数据(包括库存数量、成本金额、进价金额),则显示错误,且退出
update a set a.kcsl = a.kcsl-b.cksl,a.cbje=a.cbje -b.cbje,a.jjje=a.jjje-b.jjje,
a.cbdj=round((a.cbje-b.cbje)/(a.kcsl-b.cksl),6),a.jjdj=round((a.jjje-b.jjje)/(a.kcsl-b.cksl),6)
from Stuff_Actual a inner join
(select spdm,kf,sum(cksl) as cksl,sum(cbje) as cbje,sum(jjje) as jjje from Stuff_OutLs group by spdm,kf)b
on a.spdm=b.spdm and a.kf=b.kf
if @@error <> 0 goto Error--修改大料表中的数据
update a set a.ckds=a.ckds+b.ckds,a.cksl=a.cksl+b.cksl from Work_Stuff a left join Stuff_OutLs b
on a.id= b.Stuffid where a.id=b.Stuffid and a.gdh=@gdh and b.czy=@czy and b.kfys=1 and b.ckbz=0
if @@error <> 0 goto Error--删除临时表的数据
delete from Stuff_OutLs
if @@error <> 0 goto Error COMMIT TRANSACTION
returnerror:
begin
print '出错误了,不能出库'
rollback TRANSACTION
return
end
go--或在开始事务前加入 SET XACT_ABORT on
@@ERROR
返回最后执行的 Transact-SQL 语句的错误代码。语法
@@ERROR返回类型
integer注释
当 Microsoft® SQL Server™ 完成 Transact-SQL 语句的执行时,如果语句执行成功,则 @@ERROR 设置为 0。若出现一个错误,则返回一条错误信息。@@ERROR 返回此错误信息代码,直到另一条 Transact-SQL 语句被执行。您可以在 sysmessages 系统表中查看与 @@ERROR 错误代码相关的文本信息。由于 @@ERROR 在每一条语句执行后被清除并且重置,应在语句验证后立即检查它,或将其保存到一个局部变量中以备事后查看。注意这一句:
@@ERROR 返回此错误信息代码,直到另一条 Transact-SQL 语句被执行。
你的
if @@error = 0 COMMIT TRANSACTION
只判断
delete from Stuff_OutLs
有没出错,这个语句是很少出错的