帮忙看看这个问题

Question

declare @powerstaffid varchar(6),@newstaffid varchar(6),
@midcount int,@curcount int,@customer_guid varchar(36),@objective_guid varchar(36)
declare cursor1 cursor for select newstaffid from bk1 where flagstr=1
open cursor1
fetch next from cursor1 into @powerstaffid
while @@fetch_status=0
begin
declare cursor3 cursor for select oldstaffid from bk1 where flagstr<>1 and newstaffid=@powerstaffid
open cursor3 fetch next from cursor3 into @newstaffid
while @@FETCH_STATUS = 0
begin
select top 1 @customer_guid= customer_guid from customer20111117 where owner=@powerstaffid
update customer20111117 set owner=@newstaffid where customer_guid =@customer_guid
select top 1 @objective_guid= objective_guid from objective20111117
 where customer_guid =@customer_guid and objectivetype_id in('17974B','5172AF') and objectivestatus='OPEN'
update objective20111117 set handleby_id=@newstaffid where objective_guid=@objective_guid
update oblist20111117 set handleby_id=@newstaffid where objective_guid=@objective_guid
insert into tl(newstaffid,powerstaffid,customer_guid,objective_guid)values
(@newstaffid,@powerstaffid,@customer_guid,@objective_guid)
fetch next from cursor3 into @newstaffid
end
fetch next from cursor1 into @powerstaffid
close cursor3
deallocate cursor3 
end
close cursor1
deallocate cursor1是一个游标嵌套循环的语句块，但是我执行之后发现定义的cursor1里面还没有执行完毕，我觉得是我逻辑上的问题，麻烦大家帮忙看看这个语句块有那些问题 感谢了

Answer 1

endclose cursor3
deallocate cursor3 fetch next from cursor1 into @powerstaffid
end
close cursor1
deallocate cursor1紅色這段放錯位置

Answer 2

DECLARE @powerstaffid VARCHAR(6) ,
    @newstaffid VARCHAR(6) ,
    @midcount INT ,
    @curcount INT ,
    @customer_guid VARCHAR(36) ,
    @objective_guid VARCHAR(36)
    
DECLARE cursor1 CURSOR FOR SELECT newstaffid FROM bk1 WHERE flagstr=1
OPEN cursor1
FETCH NEXT FROM cursor1 INTO @powerstaffid
WHILE @@fetch_status = 0 
    BEGIN
        DECLARE cursor3 CURSOR FOR SELECT oldstaffid FROM bk1 WHERE flagstr<>1 AND newstaffid=@powerstaffid
        OPEN cursor3
        FETCH NEXT FROM cursor3 INTO @newstaffid
        WHILE @@FETCH_STATUS = 0 
            BEGIN
                SELECT TOP 1
                        @customer_guid = customer_guid
                FROM    customer20111117
                WHERE   owner = @powerstaffid
                UPDATE  customer20111117
                SET     owner = @newstaffid
                WHERE   customer_guid = @customer_guid
                SELECT TOP 1
                        @objective_guid = objective_guid
                FROM    objective20111117
                WHERE   customer_guid = @customer_guid
                        AND objectivetype_id IN ( '17974B', '5172AF' )
                        AND objectivestatus = 'OPEN'
                UPDATE  objective20111117
                SET     handleby_id = @newstaffid
                WHERE   objective_guid = @objective_guid
                UPDATE  oblist20111117
                SET     handleby_id = @newstaffid
                WHERE   objective_guid = @objective_guid
                INSERT  INTO tl
                        ( newstaffid ,
                          powerstaffid ,
                          customer_guid ,
                          objective_guid
                        )
                VALUES  ( @newstaffid ,
                          @powerstaffid ,
                          @customer_guid ,
                          @objective_guid
                        )
                FETCH NEXT FROM cursor3 INTO @newstaffid
            END
        CLOSE cursor3
        DEALLOCATE cursor3
        FETCH NEXT FROM cursor1 INTO @powerstaffid  
    END
CLOSE cursor1
DEALLOCATE cursor1
参考：
http://database.51cto.com/art/201009/224956.htm

Answer 3

try this.declare @powerstaffid varchar(6),@newstaffid varchar(6),@midcount int,
@curcount int,@customer_guid varchar(36),@objective_guid varchar(36)declare cursor1 cursor for select newstaffid from bk1 where flagstr=1
open cursor1
fetch next from cursor1 into @powerstaffid
while @@fetch_status=0
begin
 declare cursor3 cursor for select oldstaffid from bk1 where flagstr<>1 and newstaffid=@powerstaffid
 open cursor3 fetch next from cursor3 into @newstaffid
 while @@fetch_status=0
 begin
    select top 1 @customer_guid= customer_guid from customer20111117 where owner=@powerstaffid
    update customer20111117 set owner=@newstaffid where customer_guid =@customer_guid
    select top 1 @objective_guid= objective_guid from objective20111117
       where customer_guid =@customer_guid and objectivetype_id in('17974B','5172AF') and objectivestatus='OPEN'
    update objective20111117 set handleby_id=@newstaffid where objective_guid=@objective_guid
    update oblist20111117 set handleby_id=@newstaffid where objective_guid=@objective_guid
    insert into tl(newstaffid,powerstaffid,customer_guid,objective_guid)
       values(@newstaffid,@powerstaffid,@customer_guid,@objective_guid)
    fetch next from cursor3 into @newstaffid
 end
close cursor3
deallocate cursor3
fetch next from cursor1 into @powerstaffid
end
close cursor1
deallocate cursor1