具体的出入库操作如下:
/**出库操作 **/
begin TRANSACTION
--更新主表状态
update orderBill
set iCheck=1
where (billNo=@billNo)
if @@error <>0
begin
rollback transaction
set @rMsg='数据库更新错误!'
set @isSucc=0
return
end
--声明游标,查询明细表明细记录
declare Up_cursor CURSOR for
select WareNo,WareCountChecked,WarePrice
from BillDetail
where (billNo=@billNo)
declare @WareNo varchar(50)
declare @WareCountChecked int
declare @WarePrice money,@TotalPrice money
open Up_cursor
set @TotalPrice=0
FETCH NEXT FROM Up_cursor INTO @WareNo,@WareCountChecked,@WarePrice
WHILE @@FETCH_STATUS = 0
begin
--判断是否有该材料编码
if not exists( select iID from tWare where (WareNo=@WareNo) )
begin
rollback transaction
close Up_cursor
deallocate Up_cursor
set @rMsg='材料编码为“'+@WareNo+'”的材料不存在!'
set @isSucc=0
return
end
--判断是否大于库存
if exists( select iID from tWare where (WareNo=@WareNo) and (@WareCountChecked>WareCount))
begin
rollback transaction
close Up_cursor
deallocate Up_cursor
set @rMsg='材料“'+@WareNo+'”的审核数量大与库存数量!'
set @isSucc=0
return
end
--更新库存
update tWare set WareCount=WareCount-(@WareCountChecked) where (WareNo=@WareNo)
if @@error <>0
begin
rollback transaction
close Up_cursor
deallocate Up_cursor
set @rMsg='数据库更新错误!'
set @isSucc=0
return
end
set @TotalPrice=@TotalPrice+isnull(@WareCountChecked,0)*isnull(@WarePrice,0)
FETCH NEXT FROM Up_cursor INTO @WareNo,@WareCountChecked,@WarePrice
end
close Up_cursor
deallocate Up_cursor
commit TRANSACTION
/**出库操作结束 **/
/**入库操作 **/
begin TRANSACTION
--更新主表状态
update IntoStoreBill
set iCheck=1
where BillNo=@BillNo
if @@error <>0
begin
rollback transaction
set @isSucc=0
set @rMsg='更新数据库出错!'
return
end
--声明游标,查询明细表明细记录
declare Up_cursor CURSOR for
select WareNo,WareCount
from IntoDetailBill
where BillNo=@BillNo
declare @WareNo varchar(50)
declare @WareCount money
open Up_cursor
FETCH NEXT FROM Up_cursor INTO @WareNo,@WareCount
WHILE @@FETCH_STATUS = 0
begin
--判断是否有该材料编码
if not exists( select iID from tWare where (WareNo=@WareNo) )
begin
rollback transaction
close Up_cursor
deallocate Up_cursor
set @rMsg='材料编码为“'+@WareNo+'”的材料不存在!'
set @isSucc=0
return
end
--更新库存
update tWare set WareCount=WareCount+(@WareCount) where (WareNo=@WareNo)
if @@error <>0
begin
rollback transaction
close Up_cursor
deallocate Up_cursor
set @rMsg='数据库更新错误!'
set @isSucc=0
return
end
FETCH NEXT FROM Up_cursor INTO @WareNo,@WareCount
end
close Up_cursor
deallocate Up_cursor
commit TRANSACTION
/**入库操作结束 **/
阅读(105) | 评论(0) | 转发(0) |