SQL存储过程事务--主从表同时插入以及一般性事务处理

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