CODEWITHSUNDEEP
sqlsql-servert-sqldatabase-cursor
Daniele
Daniele

Reputation: 259

CURSOR in SQL runs multiple times when there is only 1 record in it

I am having an issue with a CURSOR where in some occasion is populated with only one value but rather than exist straight away it goes on and on till, in my case, crashes.

This is the code:

            DECLARE @positionID as INT
            DECLARE position_cursor CURSOR FOR 
            SELECT  positionUniqueNumber 
            FROM  position
            WHERE position.EmployeeID = @EmployeeID

            OPEN position_cursor  
            FETCH NEXT FROM position_cursor INTO @positionID  

            WHILE @@FETCH_STATUS = 0  
            BEGIN  
                    UPDATE   position
                    SET  positionFromDate = DATEADD(day,@days, positionFromDate)
                    WHERE position.positionUniqueNumber = @positionID

                  FETCH NEXT FROM position_cursor INTO @positionID 
            END 

            CLOSE position_cursor  
            DEALLOCATE position_cursor

I would expect the cursor to exist after one run if 'position_cursor' has only one value, instead it goes on and on till it tries to set a Date higher than 9999 and it crashes. A workaround for me would be to check how many record there are in the table that matches the criteria and use the cursor only if there is more than 1 but I would like to find a better way. Thoughts? Thanks

Upvotes: 0

Views: 2214

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88996

Here's a repro with a fix commented out. Most people expect STATIC semantics for a cursor, but it's not the default. If your update causes the row to move in the index being read by the cursor it can read the same row twice.

Just always declare your cursors as LOCAL STATIC to avoid these problems.

drop table if exists position
go
create table position(positionuniquenumber int primary key nonclustered, EmployeeId int, positionFromDate DateTime)
create unique clustered index cix_position on position(employeeid,positionFromDate,positionuniquenumber)

insert into position(positionuniquenumber,EmployeeId,positionFromDate) values (1,1,getdate())
go
DECLARE @EmployeeID int = 1
declare @Days int = 1
DECLARE @positionID as INT
DECLARE position_cursor CURSOR /*LOCAL STATIC*/ FOR 
SELECT  positionUniqueNumber 
FROM  position
WHERE position.EmployeeID = @EmployeeID

OPEN position_cursor  
FETCH NEXT FROM position_cursor INTO @positionID  

WHILE @@FETCH_STATUS = 0  
BEGIN  
        UPDATE   position
        SET  positionFromDate = DATEADD(day,@days, positionFromDate)
        WHERE position.positionUniqueNumber = @positionID

        FETCH NEXT FROM position_cursor INTO @positionID 
END 

CLOSE position_cursor  
DEALLOCATE position_cursor

Upvotes: 2

Related Questions