Cursor loop

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

*** Sent via Developersdex http://www.developersdex.com ***
.

Relevant Pages

  • Re: Cursor loop
    ... I've created a stored procedure that loops through a cursor, ... DECLARE curPeriod CURSOR LOCAL for SELECT * FROM tblPeriods ... The problem is that this loop only executes one time, ...
    (comp.databases.ms-sqlserver)
  • Re: Cursor loop
    ... DECLARE curPeriod CURSOR LOCAL for SELECT * FROM tblPeriods ... DECLARE @strTekst varchar ...
    (comp.databases.ms-sqlserver)
  • Re: ORA-06550: declaring cursor inside "begin/end" structure of PL/SQL block
    ... > This is a newbie question so please don't be too annoyed if the ... > I've written PL/SQL which gets a lists of values into a cursor. ... > list of values is then used in a loop to create another cursor ... > DECLARE ...
    (comp.databases.oracle.server)
  • Opening cursor after closing does not seem to requery
    ... I declare a cursor, then enter a loop. ... opens at the same row as the initial opening, ...
    (microsoft.public.sqlserver.programming)
  • Re: ORA-06550: declaring cursor inside "begin/end" structure of PL/SQL block
    ... > This is a newbie question so please don't be too annoyed if the ... > I've written PL/SQL which gets a lists of values into a cursor. ... > list of values is then used in a loop to create another cursor ... > DECLARE ...
    (comp.databases.oracle.server)