Posted by Erland Sommarskog on 07/12/06 22:04

Chris Zopers (test123test12@12move.nl) writes:
> 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.

This is because you check @@fetch_status before you fetch. This is how
you should write cursor loop:

DECLARE cur INSENSITIVE CURSOR FOR
SELECT col1, col2 FROM tbl

OPEN cur

WHILE 1 = 1
BEGIN
FETCH cur INTO @par1, @par2
IF @@fetch_status <> 0
BREAK

--- Do stuff
END

DEALLOCATE cur

Beyond the structure of the cursor loop, please notice:

1) Never use SELECT * with cursor declarations. Add a column to the
table, and your code breaks. That's bad.

2) The cursor must be declared as INSENSITIVE or STATIC (the latter
can be combined with LOCAL, the first cannot). With no specification
you get a dynamic cursor, which is rarely what you want. But dynamic
cursors can have bad impact on both performance and funcion.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]