Re: Cursor loop

Chris Zopers (test123test12@xxxxxxxxx) 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@xxxxxxxxxxxxx

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
.

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)
  • 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: Optimize function that uses cursors
    ... > The function can be made recursive as there are no much recursions (we ... > groups and ancestor groups (i.e. the parents of the parents and so on) for ... > a recursive call in the select of the cursor), and in the cursor look it ... > declare @more bit ...
    (microsoft.public.sqlserver.programming)
  • Re: Replace Cursor Procedure with Update Query?
    ... DECLARE @tmpResults TABLE, Create_Date Int, Seq int) ... Can I replace the below cursor based procedure with an update> query? ... Every morning, after the update,> I run the procedure below to update the Sequence field. ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Procedure Optimisation
    ... Any reason why you did not mention what DBMS product? ... I'm using a cursor to perform updates based on an ID. ... DECLARE @Site_ID NVARCHAR ... due to your EAV design of the source ...
    (comp.databases)