@@ERROR在跳出游标循环中的应用

create proc test_sy
as
declare @ERROR int,
@sql_insert nvarchar(2000),
@sql_on nvarchar(2000),
@sql_off nvarchar(2000),
@cnt int,
@cnt1 int
select @ERROR = 0
select @cnt1 = 0
set @sql_on = N'set identity_insert Recipe on ';
set @sql_off = N'set identity_insert Recipe off ';
set @sql_insert = N'insert into Db.dbo.Recipe(.....................                                      --此处造一个语法错误
            select master.* from SERVER.Db.dbo.Recipe as master
            where master.ID = 1'
declare te_cursor cursor
for select 1 from syscolumns
open te_cursor
fetch next from te_cursor into @cnt
while @@FETCH_STATUS = 0 and @cnt1 <100
begin
begin tran
exec(@sql_on+@sql_insert+@sql_off)
--if @@ERROR<>0                                                                                                  --注释掉此处
--begin
set @ERROR = @ERROR + @@ERROR
--end

set @cnt1=@cnt1+@cnt
if @ERROR <> 0
            begin
               rollback tran;
               break;
            end
            else
            begin
                commit tran;
            end
fetch next from te_cursor into @cnt
end
close te_cursor
DEALLOCATE te_cursor
select @ERROR '错误号乘以循环次数',@cnt1 '循环次数'
return @error

产生错误后立即记录@@ERROR的结果：                                      错误号*循环次数            循环次数
                                                                                                            156                               1

 

加上IF判断(绿色代码)后的执行结果：            错误号*循环次数            循环次数
                                                                       0                               100

 

分析：加了if判断后没有跳出循环，说明@@ERROR的值在加if后发生变化，变为0了

所以要使用@@ERROR还必须在sql执行的下一步就使用