July 14, 2008 at 3:05 pm
Can someone help me on this?
As stated below code hangs when the result of Select statement for first cursor(remove_cursor) return zero. If I check for result count in advance and in case its zero I don't allow the cursor to get executed then code works properly.
set @kt = (select count(*)
from ##amd_weekly_skuloc a
where amd_code='DP'
and exists (select 1
from ##future_skuloc b
where a.loc_id = b.loc_id and a.sku= b.sku))
IF @kt > 0 -- Check for zero rowcount condition
BEGIN
DECLARE remove_cursor CURSOR FOR
select statement--same as specified in frony of @kt variable above
OPEN remove_cursor
FETCH NEXT FROM remove_cursor into @loc_id, @sku
While @@fetch_status =0
BEGIN
delete CODE
fetch next from remove_cursor into @loc_id, @sku
END
close remove_cursor
deallocate remove_cursor
END -- end of @kt if
DECLARE revertdrop_cursor CURSOR FOR
select loc_id, sku
from ##amd_weekly_skuloc a
where amd_code='RD'
and exists (select 1
from ##future_skuloc b
where a.loc_id = b.loc_id and a.sku= b.sku)
OPEN revertdrop_cursor
FETCH NEXT FROM revertdrop_cursor into @loc_id, @sku
While @@fetch_status = 0
BEGIN
insert CODE
fetch next from revertdrop_cursor into @loc_id, @sku
END
close revertdrop_cursor
deallocate revertdrop_cursor
Why is it happening? Why cursor loop doesnt end up when the result set to be inserted in it returns zero?
Is it a known bug?
MJ
July 14, 2008 at 4:20 pm
My first question would be why are you using a cursor? Are you sure this cannot be done as a set-based process?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 14, 2008 at 4:47 pm
I will have to check that. Also, I found that cursor by itself doesn't know that in case resultset is empty then it shldn't proceed. To resolve this either we need to introduce a count(*) check before opening cursor or we can also make use of @@cursor_rows.
Manu
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply