Posted by Damien on 07/12/06 11:22

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.

[Back to original message]