我写的一个存储过程,用到事务,如下ALTER procedure [dbo].[P_delWaterMeter] @ID int,@returnValue int output
as
begin
declare @count int,@elementID int
begin transaction
set @returnValue=-1
select @count=COUNT(*) from WaterMeterLog where WaterMeterLog.WaterMeterID=@ID
select @elementID=ElementID from WaterMeter where WaterMeter.ID=@ID
begin
if(@count>0)
begin
delete from WaterMeterLog where WaterMeterLog.WaterMeterID=@ID
if (@@ERROR<>0 or @@ROWCOUNT=0) goto ERR
delete from WaterMeter where WaterMeter.ID=@ID
if (@@ERROR<>0 or @@ROWCOUNT=0) goto ERR
delete from Element where Element.ElementID=@elementID
if (@@ERROR<>0 or @@REMSERVER=0) goto ERR
commit transaction
set @returnValue=0
end
else
begin
delete from WaterMeter where WaterMeter.ID=@ID
if (@@ERROR<>0 or @@ROWCOUNT=0) goto ERR
delete from Element where Element.ElementID=@elementID
if (@@ERROR<>0 or @@ROWCOUNT=0) goto ERR
commit transaction
set @returnValue=0
end
end
end
ERR:
begin
rollback transaction
set @returnValue=-1
end
自己在数据库中调用存储过程,返回值逻辑正确,并没有出现“"ROLLBACK TRANSACTION 请求没有对应的 BEGIN TRANSACTION”的错误。
而我在应用程序中调用该存储过程(我只有一处调用该存储过程)!就抛出了这样的错误!
现在的问题是:
(1)该存储过程是不是有错啊?如果有的话,请帮忙修改下!由于自己对事务这块不是很熟!这里先谢谢大家了!
(2)如果是其他的问题也帮忙指出来下!
as
begin
declare @count int,@elementID int
begin transaction
set @returnValue=-1
select @count=COUNT(*) from WaterMeterLog where WaterMeterLog.WaterMeterID=@ID
select @elementID=ElementID from WaterMeter where WaterMeter.ID=@ID
begin
if(@count>0)
begin
delete from WaterMeterLog where WaterMeterLog.WaterMeterID=@ID
if (@@ERROR<>0 or @@ROWCOUNT=0) goto ERR
delete from WaterMeter where WaterMeter.ID=@ID
if (@@ERROR<>0 or @@ROWCOUNT=0) goto ERR
delete from Element where Element.ElementID=@elementID
if (@@ERROR<>0 or @@REMSERVER=0) goto ERR
commit transaction
set @returnValue=0
end
else
begin
delete from WaterMeter where WaterMeter.ID=@ID
if (@@ERROR<>0 or @@ROWCOUNT=0) goto ERR
delete from Element where Element.ElementID=@elementID
if (@@ERROR<>0 or @@ROWCOUNT=0) goto ERR
commit transaction
set @returnValue=0
end
end
end
ERR:
begin
rollback transaction
set @returnValue=-1
end
自己在数据库中调用存储过程,返回值逻辑正确,并没有出现“"ROLLBACK TRANSACTION 请求没有对应的 BEGIN TRANSACTION”的错误。
而我在应用程序中调用该存储过程(我只有一处调用该存储过程)!就抛出了这样的错误!
现在的问题是:
(1)该存储过程是不是有错啊?如果有的话,请帮忙修改下!由于自己对事务这块不是很熟!这里先谢谢大家了!
(2)如果是其他的问题也帮忙指出来下!
end
return 0; -- 问题出在这里
ERR:
ALTER procedure [dbo].[P_delWaterMeter] @ID int,@returnValue int output
as
begin
declare @count int,@elementID int
begin transaction
set @returnValue=-1
select @count=COUNT(*) from WaterMeterLog where WaterMeterLog.WaterMeterID=@ID
select @elementID=ElementID from WaterMeter where WaterMeter.ID=@ID
begin
if(@count>0)
begin
begin tran
delete from WaterMeterLog where WaterMeterLog.WaterMeterID=@ID
if (@@ERROR<>0 or @@ROWCOUNT=0) goto ERR
delete from WaterMeter where WaterMeter.ID=@ID
if (@@ERROR<>0 or @@ROWCOUNT=0) goto ERR
delete from Element where Element.ElementID=@elementID
if (@@ERROR<>0 or @@REMSERVER=0) goto ERR
commit transaction tran_1
set @returnValue=0
end
else
begin
begin tran
delete from WaterMeter where WaterMeter.ID=@ID
if (@@ERROR<>0 or @@ROWCOUNT=0) goto ERR
delete from Element where Element.ElementID=@elementID
if (@@ERROR<>0 or @@ROWCOUNT=0) goto ERR
commit transaction
set @returnValue=0
end
end
end
ERR:
begin
rollback transaction
set @returnValue=-1
end
as
begin
declare @count int,@elementID int
begin transaction
set @returnValue=-1
select @count=COUNT(*) from WaterMeterLog where WaterMeterLog.WaterMeterID=@ID
select @elementID=ElementID from WaterMeter where WaterMeter.ID=@ID
begin
if(@count>0)
begin
delete from WaterMeterLog where WaterMeterLog.WaterMeterID=@ID
if (@@ERROR<>0 or @@ROWCOUNT=0) goto ERR
delete from WaterMeter where WaterMeter.ID=@ID
if (@@ERROR<>0 or @@ROWCOUNT=0) goto ERR
delete from Element where Element.ElementID=@elementID
if (@@ERROR<>0 or @@REMSERVER=0) goto ERR
set @returnValue=0
end
else
begin
delete from WaterMeter where WaterMeter.ID=@ID
if (@@ERROR<>0 or @@ROWCOUNT=0) goto ERR
delete from Element where Element.ElementID=@elementID
if (@@ERROR<>0 or @@ROWCOUNT=0) goto ERR
set @returnValue=0
end
end
end
commit transaction
return ERR:
begin
rollback transaction
set @returnValue=-1
end
http://blog.csdn.net/xys_777/archive/2010/06/18/5678190.aspx