Re: Cursor loop
- From: "Damien" <Damien_The_Unbeliever@xxxxxxxxxxx>
- Date: 12 Jul 2006 04:22:21 -0700
Chris Zopers wrote:
Hello,Hi Chris,
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
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.
.
- References:
- Cursor loop
- From: Chris Zopers
- Cursor loop
- Prev by Date: changing records in tables
- Next by Date: SQL Server 2005 SP1 install
- Previous by thread: Cursor loop
- Next by thread: Re: Cursor loop
- Index(es):
Relevant Pages
|
