Cursor won''t fetch next row?
-
Does anyone know what could cause a cursor to continue to fetch the same row over and over again?
I have a cursor declared with dynamic sql. The cursor is opened, an update is done using dynamic sql, and then moved to the next row in the cursor. The problem I'm having is the cursor never moves to the next row, it continues to fetch the same row for infinity. If I remove the update and just print the dynamic sql, the statements looks perfect and run as intended when cut and pasted into a new query window.
What can cause the "fetch next" to continue to return the same row?
Here is some of the code - I don't know how much help it is not in context:
set @sql = 'declare muCursor cursor fast_forward for select sku, markup, id_cont from ItemPrice_Store' + isnull( @ConditionClause, '' )
exec (@sql)
set @rows = 0
open muCursor
while (0=0) begin
fetch next from muCursor into @sku, @markup, @id_cont
if @@fetch_status <> 0 break
if @markup is null continue
select @sql = 'update ItemPrice_Store set pluprice = ' + formula + ' where sku = ''' + @sku + ''' and id_cont = ' + ltrim(str(@id_cont)) from markup where markup = @markup
exec (@sql)
set @rows = @rows + 1
end
close muCursor
deallocate muCursor
-
I guess your cursor is built under wrong syntax. I think you missed one 'fetch next' command. For more info see BOL
It's only my personal oppinion
-
Try declaring your cursor as static like
'declare muCursor cursor static for ...
It is likely the cursor is resetting when the table data changes from your update.
I should also add that you should explore the possibility of a set based solution that would perform better than the cursor.
HTH
-
I did not verified your total script but the basic thing missing is fetch system inside the loop
set @sql = 'declare muCursor cursor fast_forward for select sku, markup, id_cont from ItemPrice_Store' + isnull( @ConditionClause, '' )
exec (@sql)
set @rows = 0
open muCursor
while (0=0) begin
fetch next from muCursor into @sku, @markup, @id_cont
if @@fetch_status <> 0 break
if @markup is null continue
select @sql = 'update ItemPrice_Store set pluprice = ' + formula + ' where sku = ''' + @sku + ''' and id_cont = ' + ltrim(str(@id_cont)) from markup where markup = @markup
exec (@sql)
set @rows = @rows + 1
fetch next from muCursor into @sku, @markup, @id_cont
end
close muCursor
deallocate muCursor
Prasad Bhogadi
www.inforaise.com -
?? I thought that the "fetch next" line right after the while loop begins is in the loop. But two posters have said the problem is with the fetch next? Can you clarify why the way the code is setup now is a problem?
I also thought that the fast_forward declaration created a static cursor, but now reviewing BOL it does not confirm. I will try adding the static keyword and let you guys know what happens. Thanks!
-
Both Prasad and Vidas are referring to a more common syntax where you do one fetch outside the loop, then another inside like.
FETCH NEXT INTO
WHILE @@FETCH_STATUS = 0
BEGIN
...
...
FETCH NEXT INTO
END
Your syntax should still work though.
-
More common, maybe, but does that necessarily mean it should be done that way? Why use two FETCH NEXT statements when you can easily use just one?
-
The scenario I've seen it used is when you don't want it to enter the loop if the initial fetch doesn't return anything (I failed to include an IF check in the sample). But you are correct. If it's not necessary to have two fetch statements then it doesn't make sense to code it that way.
-
Changing the curosr from fast_forward to static (they are mutually exclusive) resolved the issue!!! The client claims that it worked fine up until last week, but this seems to prove that it never would have worked.
I've always used the while loop with a single fetch next statement, as it makes more sense to me logically.
Thank you everyone for your help - this issue was really bugging me!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply