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.
