SQLTeam.com | Weblogs | Forums

SQL Server 2012 @@FETCH_STATUS problem


#1

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?


#2

but then your very next line has [quote="marcosfcosantos, post:1, topic:9494"]
while @@fetch_status <> -1
[/quote]

so it will never execute.

but then, why do you need an inner loop? Surely a simple update command will do the job?


#3

Your loop also uses "@wided" and "@vidMaster", but I don't see any code setting those values.


#4

Your code here may be simplified for posting on the forum, but can you not do this set-based, rather than row-by-agonising-row cursor-based?

My guestimate is that the Cursor will be at eklast 100x slower than a set-based solution