SQL Cursors

Posted: April 19, 2010 in Cursors, TSQL

I had thought that if an item came within teh scope of a cursor it would remain there until removed. However, cursors work a little like views, if you cange a value which would remove an item from a view you will no longer see it in the view. If you change the value of a data row which would put it outside the scope of teh cursor the cursor will drop that row.

This was tested on SQL 2005 sp3 – I assume it will be the same on other versions but I haven’t tested them.

I have a simple table:

CREATE TABLE [dbo].[test](

[pkid] [int] IDENTITY(1,1) NOT NULL,

[descript] [varchar](5) NOT NULL,

[value1] [int] NOT NULL,

[value2] [int] NOT NULL,

CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED

(

[pkid] ASC

);

The table has 3 rows:

Pkid descript value1 value2
1 test1 0 0
2 test2 0 0
3 test1 0 0

I then execute this script against the table:

Declare @dbName as varchar(5);

Declare @x as int;

Set @x = 1;

DECLARE dbCursor CURSOR Local Fast_Forward FOR

select descript From dbo.Test

WHERE value2 = 0;

OPEN dbCursor

FETCH NEXT FROM dbCursor INTO @dbName

WHILE @@FETCH_STATUS = 0

BEGIN

Print @dbName

Update dbo.Test

Set value2 = @x

Where descript = @dbname;

Set @x = @x+1;

FETCH NEXT FROM dbCursor INTO @dbName;

END

CLOSE dbCursor;

DEALLOCATE dbCursor;

The results in the table are:

Pkid descript value1 value2
1 test1 0 1
2 test2 0 2
3 test1 0 1

This shows that because the value2 value of pkid 3 changed from 0 to 1 on the first update it dropped out of the cursors scope.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s