sql server 2000 ，事务中的数据更新语句未提交前，@@rowcount 的值有效吗？

[21lyw]

如题

[gutlgutl]

在本事务内可以看到，在别的连接内是无效的

[昵称被占用了]

如果是

BEGIN TRANSACTION

...
update ...
if @@rowcount ...  
...

COMMIT TRANSACTION

这样的语句，是有效的

[w2jc]

在事务内，只要执行了update，@@rowcount就是有效的

[21lyw]

下面是代码，屏蔽红色行代码后，能正确提交修改。不屏蔽时，就执行了蓝色代码



use swgl

declare @qsph char(8)
declare @jzph char(8)
declare @dpr char(20)
declare @retractCount int
declare @errMsg char(200)


declare @disposeCount_1 int
declare @disposeCount_2 int


set @qsph='80000001'
set @jzph='80000010'
set @dpr='赵艳桃'




begin tran retract

update t_print_billlist
set flag='0',kjsj=NULL,kjr=''
where ltrim(rtrim(kcr))=ltrim(rtrim(@dpr)) and
ltrim(rtrim(kjr))=ltrim(rtrim(@dpr)) and
flag='1' and
bill>=@qsph and
bill<=@jzph
--if @@error <> 0 goto E_Retract_Failset @disposeCount_1 = @@rowcount
print @disposeCount_1

delete from t_print_log
where ltrim(rtrim(optioner))=ltrim(rtrim(@dpr)) and
number>=@qsph and
number<=@jzph and
flag='1'
--if @@error <> 0 goto E_Retract_Failset @disposeCount_2 = @@rowcount
print @disposeCount_2

-- 检查 处理的数据个数
if @disposeCount_1 <> @disposeCount_2
begin
print 'check_1'
set @retractCount = -1
set @errMsg = '表 t_print_billlist 和 t_print_log 中的数据个数不一致。'
rollback tran retract
return
end
if @disposeCount_1 = @disposeCount_2 and @disposeCount_1 = 0
begin
print 'check_2'
set @retractCount = 0
set @errMsg = '没有要撤销的数据。'
rollback tran retract
return
end
-- 条件符合，可以提交
print 'commit'
commit tran retract
if @@error <> 0 goto E_Retract_Fail
set @retractCount = @disposeCount_1
return

E_Retract_Fail:
print 'Fail'
rollback tran retract
set @retractCount = -1
set @errmsg = '发生错误，撤销未完成'
return

print 'end'

go

[昵称被占用了]

这样改

use   swgl 

declare       @qsph   char(8) 
declare       @jzph   char(8) 
declare       @dpr     char(20) 
declare       @retractCount   int 
declare       @errMsg   char(200) 

declare   @e   int         --增加变量，用于存放@@error

declare   @disposeCount_1   int 
declare   @disposeCount_2   int 


set   @qsph='80000001' 
set   @jzph='80000010' 
set   @dpr='赵艳桃' 


begin   tran   retract 

update   t_print_billlist   
    set   flag='0',kjsj=NULL,kjr=''   
    where   ltrim(rtrim(kcr))=ltrim(rtrim(@dpr))   and   
                ltrim(rtrim(kjr))=ltrim(rtrim(@dpr))   and   
                flag='1'   and   
                bill> =@qsph   and   
                bill <=@jzph 
select @e=@@error,@disposeCount_1   =   @@rowcount 

if   @e   <>   0   goto   E_Retract_Fail

print   @disposeCount_1 

delete   from   t_print_log   
    where   ltrim(rtrim(optioner))=ltrim(rtrim(@dpr))   and   
                number> =@qsph   and   
                number <=@jzph   and   
                flag='1'   
select @e=@@error,@disposeCount_2   =   @@rowcount 

if   @e   <>   0   goto   E_Retract_Failset

print   @disposeCount_2

--   检查   处理的数据个数 
if   @disposeCount_1   <>   @disposeCount_2 
begin 
    print   'check_1' 
    set   @retractCount   =   -1 
    set   @errMsg   =   '表   t_print_billlist   和   t_print_log   中的数据个数不一致。' 
    rollback   tran   retract 
    return 
end   
if   @disposeCount_1   =   @disposeCount_2   and   @disposeCount_1   =   0 
begin 
    print   'check_2' 
    set   @retractCount   =   0 
    set   @errMsg   =   '没有要撤销的数据。' 
    rollback   tran   retract 
    return 
end   
--   条件符合，可以提交 
print   'commit' 
commit   tran   retract 
if   @@error   <>   0   goto   E_Retract_Fail 
set   @retractCount   =   @disposeCount_1 
return 

E_Retract_Fail: 
        print   'Fail' 
        rollback   tran   retract 
        set   @retractCount   =   -1 
        set   @errmsg   =   '发生错误，撤销未完成' 
        return 

print   'end' 

go 
 

[昵称被占用了]

晕，代码里不能设置颜色，搂主把[color=# FF0000]和[/ color]去掉

[21lyw]

这样改了后，数据能正常处理了，但是处理了 5 条数据后，@e 却显示 1 。

[21lyw]

我找到问题了：因为在取 @@rowcount 的值前，我先取了 @@error 的值，所以@@rowcount 的值已经不是 update 语句的影响行数了。