Re: Cursor loop

Chris Zopers wrote:
Hello,

I've created a stored procedure that loops through a cursor, with the
following example code:

DECLARE curPeriod CURSOR LOCAL for SELECT * FROM tblPeriods
DECLARE @intYear smallint
DECLARE @intPeriod smallint
DECLARE @strTekst varchar(50)

OPEN curPeriod

WHILE @@FETCH_STATUS=0

BEGIN

FETCH NEXT FROM curPeriod INTO @intYear, @intPeriod

SET @strTekst = CONVERT(varchar, @intPeriod)

PRINT @strTekst

END

CLOSE curPeriod
DEALLOCATE curPeriod

The problem is that this loop only executes one time, when I call the
stored procedure a second or third time, nothing happens. It seems that
the Cursor stays at the last record or that @@Fetch_status isn't 0. But
I Deallocate the cursor. I have to restart the SQL Server before the
stored procedure can be used again.

Does anyone know why the loop can execute only 1 time?

Greetings,
Chris
Hi Chris,

When you say you have to restart SQL Server before it can be used
again, do you mean the server or just Query Analyser?

I suspect the issue you're having is when you next enter the stored
procedure, the FETCH_STATUS is still as it was at the end of the last
time through the loop - non-zero, and so the loop isn't executed.

I've never seen a good pattern for doing cursors that doesn't look
messy (Since most practicioners tend to try to avoid them in the first
place, no-one spends much time tidying them up).

Normal pattern for me is:

declare cursor x for select ...
declare <variables to hold the columns>

open x

fetch next from x into <list of variables>
while @@FETCH_STATUS = 0
begin
--Do stuff

fetch next from x into <list of variables>
end

close x
deallocate x

in short, I've never found a way to do it which doesn't have to have
the same fetch statement in two places.

Damien

PS - Usual recommendation would be to have a list of columns, rather
than select * from.... However, there is disagreement over this
particular recommendation, I'd suggest you search the archives for some
lively debate on the matter.

.

Relevant Pages