I wrote a TSQL Script to repair my customer's database. This script contains a cursor based on a SELECT command that performs a JOIN between a master and a detail tables.This code is simillar to above:
declare
@idMaster int,
@idDetail int,
@MasterField varchar(6),
@DetailField varchar(40),
@vidMater int
declare c cursor local static for
select m.idMaster, d.idDetail, m.MasterField, d.DetailField
from Master m join Detail d on m.idMaster = d.idMaster
open c
fetch c into @idMaster, @idDetail, @MasterField, @DetailField
while @@fetch_status <> -1
begin
-- Do some processing
insert into MyTable select * from Master where idMaster = @idMaster
set @vidMaster = @wided
while @@fetch_status <> -1 and @idMaster = @vidMaster
begin
update MyTable2 set SomeField = SomeField + 1 where id = @idDetail
fetch c into @idMaster, @idDetail, @MasterField, @DetailField
end
end
close c
deallocate c
The issue is that the nested loop (that contains the "update' command) is never executed. In debug, I verified that the @@fetch_status variable was changed to -1 after the "insert into MyTable" command.
It is correct? Or is a bug? Is there any enviroment variable that controls this behavior?