我在SQL中写了一段存储过程,通过提交事务的形式来做,中间用游标的形式来进行表的循环,在提交时如果不成功,要求回滚,但是在运行时达不到预期的目的,请大家给我看一下.CREATE PROCEDURE sp_qckc_add @message char(4) outputASbegin tran qckcdj --定义游标
-- 期初编号从1号开始 declare @qcbh char(10),@qcrq datetime,@qc_spbh char(8),@qc_qcsl float,@qc_qcdj float,@qc_qcje float,@qc_ckbh char(2)
declare @qcbhx int
set @qcbhx=1
set @qcrq=(select jzrq from syscsb) declare qcmx_cus cursor for select spbh,ckbh,qcsl,qcdj,qcje from qckcdjb for update open qcmx_cus fetch next from qcmx_cus into @qc_spbh,@qc_ckbh,@qc_qcsl,@qc_qcdj,@qc_qcje while @@fetch_status=0
begin
set @qcbh='QCKC'+right(cast(@qcbhx+1000000 as char(7)),6)
set @qcbhx=@qcbhx+1 insert spzy values(@qc_spbh,@qcbh,@qcrq,@qc_qcsl,@qc_qcdj,@qc_qcje)
declare @jlgs int,@jlgs1 int set @jlgs=(select count(*) from ckzb where spbh=@qc_spbh)
if @jlgs=0
insert ckzb values(@qc_spbh,0)
update ckzb set kcsl=kcsl+@qc_qcsl where spbh=@qc_spbh
fetch next from qcmx_cus into @qc_spbh,@qc_ckbh,@qc_qcsl,@qc_qcdj,@qc_qcje
end close qcmx_cus deallocate qcmx_cus
update syscsb set qckccsh='1'commit tran qckcdj
if @@error!=0
begin
set @message='失败'
rollback tran qckcdj
end
else
set @message='成功'
GO
-- 期初编号从1号开始 declare @qcbh char(10),@qcrq datetime,@qc_spbh char(8),@qc_qcsl float,@qc_qcdj float,@qc_qcje float,@qc_ckbh char(2)
declare @qcbhx int
set @qcbhx=1
set @qcrq=(select jzrq from syscsb) declare qcmx_cus cursor for select spbh,ckbh,qcsl,qcdj,qcje from qckcdjb for update open qcmx_cus fetch next from qcmx_cus into @qc_spbh,@qc_ckbh,@qc_qcsl,@qc_qcdj,@qc_qcje while @@fetch_status=0
begin
set @qcbh='QCKC'+right(cast(@qcbhx+1000000 as char(7)),6)
set @qcbhx=@qcbhx+1 insert spzy values(@qc_spbh,@qcbh,@qcrq,@qc_qcsl,@qc_qcdj,@qc_qcje)
declare @jlgs int,@jlgs1 int set @jlgs=(select count(*) from ckzb where spbh=@qc_spbh)
if @jlgs=0
insert ckzb values(@qc_spbh,0)
update ckzb set kcsl=kcsl+@qc_qcsl where spbh=@qc_spbh
fetch next from qcmx_cus into @qc_spbh,@qc_ckbh,@qc_qcsl,@qc_qcdj,@qc_qcje
end close qcmx_cus deallocate qcmx_cus
update syscsb set qckccsh='1'commit tran qckcdj
if @@error!=0
begin
set @message='失败'
rollback tran qckcdj
end
else
set @message='成功'
GO
begin
set @message='失败'
rollback tran
end
commit tran
CREATE PROCEDURE sp_qckc_add @message char(4) output AS begin tran --qckcdj --定义游标
-- 期初编号从1号开始 declare @qcbh char(10),@qcrq datetime,@qc_spbh char(8),@qc_qcsl float,@qc_qcdj float,@qc_qcje float,@qc_ckbh char(2)
declare @qcbhx int
set @qcbhx=1
set @qcrq=(select jzrq from syscsb) declare qcmx_cus cursor for select spbh,ckbh,qcsl,qcdj,qcje from qckcdjb for update open qcmx_cus fetch next from qcmx_cus into @qc_spbh,@qc_ckbh,@qc_qcsl,@qc_qcdj,@qc_qcje while @@fetch_status=0
begin
set @qcbh='QCKC'+right(cast(@qcbhx+1000000 as char(7)),6)
set @qcbhx=@qcbhx+1 insert spzy values(@qc_spbh,@qcbh,@qcrq,@qc_qcsl,@qc_qcdj,@qc_qcje)
declare @jlgs int,@jlgs1 int set @jlgs=(select count(*) from ckzb where spbh=@qc_spbh)
if @jlgs=0
insert ckzb values(@qc_spbh,0)
update ckzb set kcsl=kcsl+@qc_qcsl where spbh=@qc_spbh
fetch next from qcmx_cus into @qc_spbh,@qc_ckbh,@qc_qcsl,@qc_qcdj,@qc_qcje
end close qcmx_cus deallocate qcmx_cus
update syscsb set qckccsh='1'
set @message='成功' /*commit tran qckcdj */
if @@error <> 0
rollback tran --qckcdj
set @message='失败'
commit tran --qckcdj --放在后面了GO 或:
修改如下:CREATE PROCEDURE sp_qckc_add @message char(4) output AS begin tran --qckcdj --定义游标
-- 期初编号从1号开始 declare @qcbh char(10),@qcrq datetime,@qc_spbh char(8),@qc_qcsl float,@qc_qcdj float,@qc_qcje float,@qc_ckbh char(2)
declare @qcbhx int
set @qcbhx=1
set @qcrq=(select jzrq from syscsb) declare qcmx_cus cursor for select spbh,ckbh,qcsl,qcdj,qcje from qckcdjb for update open qcmx_cus fetch next from qcmx_cus into @qc_spbh,@qc_ckbh,@qc_qcsl,@qc_qcdj,@qc_qcje while @@fetch_status=0
begin
set @qcbh='QCKC'+right(cast(@qcbhx+1000000 as char(7)),6)
set @qcbhx=@qcbhx+1 insert spzy values(@qc_spbh,@qcbh,@qcrq,@qc_qcsl,@qc_qcdj,@qc_qcje)
declare @jlgs int,@jlgs1 int set @jlgs=(select count(*) from ckzb where spbh=@qc_spbh)
if @jlgs=0
insert ckzb values(@qc_spbh,0)
update ckzb set kcsl=kcsl+@qc_qcsl where spbh=@qc_spbh
fetch next from qcmx_cus into @qc_spbh,@qc_ckbh,@qc_qcsl,@qc_qcdj,@qc_qcje
end close qcmx_cus deallocate qcmx_cus
update syscsb set qckccsh='1' 、
set @message='失败' /*commit tran qckcdj */
if @@error<>0 goto err_Handle
commit tran qckcdj --放在后面了
err_Handle:
ROLLBACK TRANSACTION
set @message='失败'
GO
set @message='成功' /*commit tran qckcdj */
if @@error <> 0 goto err_Handle
commit tran --qckcdj --放在后面了 err_Handle:
ROLLBACK TRANSACTION
set @message='失败'
GO
if @@error!=0 其中@@error返回的是上一条语句执行的错误代码。
你的逻辑里面,上一句是
commit tran qckcdj
肯定不会有问题的。所以你永远不会Rollback。
正确的@@error的用法是在每一条Insert或者Update之后都加上这样的判断。而不是到最后再判断。