我写了一个事务,大家帮忙看一下
我是执行一条语句就用 @@rowcount 获取它受影响的行数,然后判断是否要回滚。有那个必要吗??望高手解答alter proc procBillOutOrder @storeOrderID nvarchar(50),@outStorageOrderID nvarchar(50),
@DocTypeID int,@DocMaker varchar(12),@Client varchar(50),@DepotSeatID int,@WareHouseID int,
@TotalMoney numeric,@TotalPV numeric,@ExpectNum int,
@Cause varchar(100),@Note varchar(500),@StateFlag int,@CloseFlag int,
@OperationPerson varchar(20),@OriginalDocID varchar(20),@Address varchar(50),
@ProductID int,@ProductQuantity numeric,@UnitPrice numeric,@MeasureUnit varchar(15),
@PV numeric,@ProductTotal numeric,@rt nvarchar(50) output
as
begin tran
declare @issent nvarchar(1)
declare @TotalIn numeric
declare @TotalOut numeric
select @issent=IsSent from StoreOrder where StoreOrderID=@storeOrderID
select @TotalIn=TotalIn,@TotalOut=TotalOut from ProductQuantity where ProductID=@ProductID and DepotSeatID=@DepotSeatID and WareHouseID=@WareHouseID
if(@issent='N')
begin
declare @zc numeric
select @zc=@ProductQuantity+@TotalOut
if(@zc>@TotalIn)
begin
select @rt='0' --库存不够
rollback tran
return
end
else
begin
select @rt='1' --库存足够,正确
update dbo.ProductQuantity set TotalOut = @zc where ProductID=@ProductID and DepotSeatID=@DepotSeatID and WareHouseID=@WareHouseID
if(@@rowcount=0)
begin
select @rt='-1'
rollback tran
return
end
end
end
update dbo.StoreOrder set IsGeneOutBill='Y',OutStorageOrderID=@outStorageOrderID where StoreOrderID=@storeOrderID
if(@@rowcount=1)
begin
insert into InventoryDoc(DocTypeID, DocID, DocMaker,
Client, DepotSeatID, WareHouseID, TotalMoney, TotalPV,
ExpectNum, Cause, Note, StateFlag,
CloseFlag, BatchCode, OperationPerson, OriginalDocID,
Address)
values(@DocTypeID,@outStorageOrderID,@DocMaker,@Client,
@DepotSeatID,@WareHouseID,@TotalMoney,@TotalPV,@ExpectNum,
@Cause,@Note,@StateFlag,@CloseFlag,'0',@OperationPerson,@OriginalDocID,
@Address) declare @iden nvarchar(50)
select @iden=convert(nvarchar,@@identity) if(@@rowcount=1)
begin
insert into dbo.InventoryDocDetails(DocID,ProductID,ProductQuantity,UnitPrice,MeasureUnit,PV,
ExpectNum,ProductTotal,DepotSeatID)
values(@outStorageOrderID,@ProductID,@ProductQuantity,@UnitPrice,@MeasureUnit,@PV,@ExpectNum,
@ProductTotal,@DepotSeatID) if(@@rowcount=1)
begin
update dbo.InventoryDoc set BatchCode='PC'+convert(nvarchar,getdate(),12)+'000'+@iden where ID=@iden
if(@@rowcount=1)
begin
select @rt='1'
end
else
begin
select @rt='-2' --失败
rollback tran
return
end
end
else
begin
select @rt='-3'
rollback tran
return
end
end
else
begin
select @rt='-4'
rollback tran
return
end
end
else
begin
select @rt='-5'
rollback tran
return
end
commit tran
我是执行一条语句就用 @@rowcount 获取它受影响的行数,然后判断是否要回滚。有那个必要吗??望高手解答alter proc procBillOutOrder @storeOrderID nvarchar(50),@outStorageOrderID nvarchar(50),
@DocTypeID int,@DocMaker varchar(12),@Client varchar(50),@DepotSeatID int,@WareHouseID int,
@TotalMoney numeric,@TotalPV numeric,@ExpectNum int,
@Cause varchar(100),@Note varchar(500),@StateFlag int,@CloseFlag int,
@OperationPerson varchar(20),@OriginalDocID varchar(20),@Address varchar(50),
@ProductID int,@ProductQuantity numeric,@UnitPrice numeric,@MeasureUnit varchar(15),
@PV numeric,@ProductTotal numeric,@rt nvarchar(50) output
as
begin tran
declare @issent nvarchar(1)
declare @TotalIn numeric
declare @TotalOut numeric
select @issent=IsSent from StoreOrder where StoreOrderID=@storeOrderID
select @TotalIn=TotalIn,@TotalOut=TotalOut from ProductQuantity where ProductID=@ProductID and DepotSeatID=@DepotSeatID and WareHouseID=@WareHouseID
if(@issent='N')
begin
declare @zc numeric
select @zc=@ProductQuantity+@TotalOut
if(@zc>@TotalIn)
begin
select @rt='0' --库存不够
rollback tran
return
end
else
begin
select @rt='1' --库存足够,正确
update dbo.ProductQuantity set TotalOut = @zc where ProductID=@ProductID and DepotSeatID=@DepotSeatID and WareHouseID=@WareHouseID
if(@@rowcount=0)
begin
select @rt='-1'
rollback tran
return
end
end
end
update dbo.StoreOrder set IsGeneOutBill='Y',OutStorageOrderID=@outStorageOrderID where StoreOrderID=@storeOrderID
if(@@rowcount=1)
begin
insert into InventoryDoc(DocTypeID, DocID, DocMaker,
Client, DepotSeatID, WareHouseID, TotalMoney, TotalPV,
ExpectNum, Cause, Note, StateFlag,
CloseFlag, BatchCode, OperationPerson, OriginalDocID,
Address)
values(@DocTypeID,@outStorageOrderID,@DocMaker,@Client,
@DepotSeatID,@WareHouseID,@TotalMoney,@TotalPV,@ExpectNum,
@Cause,@Note,@StateFlag,@CloseFlag,'0',@OperationPerson,@OriginalDocID,
@Address) declare @iden nvarchar(50)
select @iden=convert(nvarchar,@@identity) if(@@rowcount=1)
begin
insert into dbo.InventoryDocDetails(DocID,ProductID,ProductQuantity,UnitPrice,MeasureUnit,PV,
ExpectNum,ProductTotal,DepotSeatID)
values(@outStorageOrderID,@ProductID,@ProductQuantity,@UnitPrice,@MeasureUnit,@PV,@ExpectNum,
@ProductTotal,@DepotSeatID) if(@@rowcount=1)
begin
update dbo.InventoryDoc set BatchCode='PC'+convert(nvarchar,getdate(),12)+'000'+@iden where ID=@iden
if(@@rowcount=1)
begin
select @rt='1'
end
else
begin
select @rt='-2' --失败
rollback tran
return
end
end
else
begin
select @rt='-3'
rollback tran
return
end
end
else
begin
select @rt='-4'
rollback tran
return
end
end
else
begin
select @rt='-5'
rollback tran
return
end
commit tran
不一定要看代码的。只要回答这个就可以了:我是执行一条语句就用 @@rowcount 获取它受影响的行数,然后判断是否要回滚。有那个必要吗??望高手解答
指定当 Transact-SQL 语句出现运行时错误时,SQL Server 是否自动回滚到当前事务。
那如果我更新一条记录时,0行受影响。SET XACT_ABORT { ON | OFF } 能捕获到吗?
@gzid char(20)
,@djlxbs char(3)
as--每一过程拥有一唯一区界号,过程内的异常编号在此基础上增加
declare @errcode integer
set @errcode = 150
declare @return integer /*返回结果的初始化*/
set @return = 0
declare
@djbh char(14)
,@rq char(10)
,@bm char(20)
,@ywy char(20)
,@hw char(11)
,@username char(20)
,@jzhh char(11)
,@zhy char(30)
,@tmp_zoo dec(14,2)
,@m int
,@bendian char(3)--初始化语句
--启动事务处理
declare @tran_point int --控制事务嵌套
set @tran_point=@@trancount --保存事务点
if @tran_point=0
begin tran tran_bsdj
else
save tran tran_bsdj
--处理语句
if @@error<>0 --明细数据提取失败
begin
set @return=4
goto err_lab
end
--事务处理
if @tran_point = 0
commit tran tran_bsdj
goto return_laberr_lab:
if @return < 100
set @return = @errcode + @return
rollback tran tran_bsdjreturn_lab:
return @return一般写过程我这样写!
if(@@rowcount=0)
begin
select @rt='-1'
rollback tran
return
象以上的语句可update前先用 if exists()
查询记录是否存在,存在再update
insert into InventoryDoc(DocTypeID, DocID, DocMaker,
Client, DepotSeatID, WareHouseID, TotalMoney, TotalPV,
ExpectNum, Cause, Note, StateFlag,
CloseFlag, BatchCode, OperationPerson, OriginalDocID,
Address)
values(@DocTypeID,@outStorageOrderID,@DocMaker,@Client,
@DepotSeatID,@WareHouseID,@TotalMoney,@TotalPV,@ExpectNum,
@Cause,@Note,@StateFlag,@CloseFlag,'0',@OperationPerson,@OriginalDocID,
@Address)
insert语句如果插入不成功,一般会直接产生错误而使程序中断