主从表同时插入以及一般性事务处理,觉得不太理想,不知道大家有没好的方法。...

 

主从表插入以及一般性事务处理
/*
添加采购入库数据
1.插入主表
2.插入从表
3.更新最新进价、库存状态
4.更新导入状态
*/
ALTER Proc AddPurcaseInStoreRoom
@PurcaseInStoreRoomNo Char(30),
@InDate SmallDatetime,
@ProviderID Int,
@ArrivePurchaseInspectID Int,
@ArrivePurchaseInspectNo Char(30),
@Remark Varchar(100),
@TotalAmount Money,
@InputEmployeeID Int,
@AddDetailSql Varchar(500)
As
Declare @newId int, @State int, @tmpPid int, @tmpSid int, @tmpPrice money, @Pno Varchar(30), @Count int, @tmpstr Varchar(500)
Set @State = 0

Begin Tran
--插入主表
Insert Into tPurcaseInStoreRoom (PurcaseInStoreRoomNo, InDate, ProviderID, ArrivePurchaseInspectID, ArrivePurchaseInspectNo, Remark, IsSettled, TotalAmount, SettledAmount, RealPayAmount, InputEmployeeID, InputDateTime)
Values (@PurcaseInStoreRoomNo, @InDate, @ProviderID, @ArrivePurchaseInspectID, @ArrivePurchaseInspectNo, @Remark, 0, @TotalAmount, 0, 0, @InputEmployeeID, GetDate())

If @@Error <> 0
Begin
Rollback Tran
Return -1
End
Set @newId = @@Identity

--插入从表
Set @tmpstr = Convert(Varchar, @newId)
Set @tmpstr = Replace(@AddDetailSql, '-Id', @tmpstr)
Exec(@tmpstr)

If @@Error <> 0
Begin
Rollback Tran
Return -1
End

--更新商品最新进价以及库存状态
Declare Temp_Cursor Cursor
For
Select ProductID, StoreRoomID, Price, BatchNo, Amount From tPurcaseInStoreRoomDetail Where PurcaseInStoreRoomID = @newId

Open Temp_Cursor
--如果没有任何行则直接退出
If @@Cursor_Rows = 0
Begin
Close Temp_Cursor
Deallocate Temp_Cursor
End

While @State = 0
Begin
Fetch Temp_Cursor Into @tmpPid, @tmpSid, @tmpPrice, @Pno, @Count
Select @State = @@Fetch_Status

--修改最近进价
Update tProduct Set RecCostPrice = @tmpPrice Where ProductID = @tmpPid And RecCostPrice != @tmpPrice

--修改库存信息
--
如果该批号商品存在于数据库中则更新
if (Exists(Select StoreRoomID From tStock Where ProductID = @tmpPid And BatchNo = @Pno))
Update tStock Set Amount = Amount + @Count Where ProductID = @tmpPid And BatchNo = @Pno
else
Insert Into tStock (StoreRoomID, ProductID, BatchNo, ProviderID, [ExpireDate], Amount, CostPrice, IsStopSale)
Values (@tmpSid, @tmpPid, @Pno, @ProviderID, GetDate(), @Count, @tmpPrice, 0)

If @@Error <> 0
Begin
Rollback Tran
Close Temp_Cursor
Deallocate Temp_Cursor
Return -1
End
End

Close Temp_Cursor
Deallocate Temp_Cursor

--修改导入状态
Update tArrivePurchaseInspect Set IsCite = 1 Where ArrivePurchaseInspectID = @ArrivePurchaseInspectID

If @@Error <> 0
Begin
Rollback Tran
Return -1
End
Commit Tran
Return @newId

 

转载于:https://www.cnblogs.com/osoft1983/archive/2008/10/22/1316370.html

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值