T-SQL – Cursors

Cursors allow to process data row by row. Such processing is relatively slow.

Simple cursor usage.

DECLARE myCursor CURSOR FAST_FORWARD FOR  -- declare cursor
SELECT lastname, title FROM HR.Employees;

DECLARE @name AS NVARCHAR(255); -- declare variables which will be set by cursor
DECLARE @title AS NVARCHAR(25);

OPEN myCursor
FETCH NEXT FROM myCursor INTO @name, @title; -- get first record from cursor
WHILE  @@FETCH_STATUS = 0 --check if there is any record
	BEGIN
		PRINT 'Processing: ' + @name + ' (' + @title + ')'; -- process row data
		FETCH NEXT FROM myCursor INTO @name, @title; -- read next row
	END
CLOSE myCursor; -- finish work
DEALLOCATE myCursor;

There are different types of cursor:
a) INTENSIVE – makes a temporary copy of real table and operates on this copy.
b) SCROLL – allows you to move to the first/ last/ nth row.
c) READ_ONLY – updating is not allowed.
d) FORWARD_ONLY – allows to get always only the next row (not i.e. the last one).
e) FAST_FORWARD – means FORWARD_ONLY and READ_ONLY.
f) FOR UPDATE – allows to update specific columns.

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