Cursor in SQL Server 2005

Cursor Declaration


DECLARE cursor_name CURSOR
[ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,…n ] ] ]


[ LOCAL | GLOBAL ]

This specifies the Scope of Cursors. LOCAL means that this CURSOR is local to Batch, Stored Procedure or Trigger in which it is created and is only valid in within this scope.

GLOBAL specifies that the scope of CURSOR is global to connection and can be referenced in any Stored Procedure, Trigger or Batch executed by this Connection.

However, if neither GLOBAL nor LOCAL is specified, the default will be LOCAL.

[ FORWARD_ONLY | SCROLL ]

It defines the Scroll ability of CURSOR. FORWARD_ONLY cursors can fetch only the next row whereas SCROLL cursors fetch rows in any direction and by any number.

However, FORWARD_ONLY cursors perform faster.

[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]

STATIC CURSOR has no real time requirement. It just copies Data in Result Set into TEMPDB and fetches row from TEMPDB.

KEYSET CURSOR copies the primary key data into TEMPDB. This is the default CURSOR type. It is more time consuming than DYNAMIC but requires less resources.

DYNAMIC CURSOR also stores keys but the key data is refreshed with each modification to key data on base tables and thus it can keep track of inserted, deleted or modified rows. This is expensive in terms of used resources.

FAST_FORWARD CURSOR only fetches the next row and provides optimal performance.

[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]

READ ONLY – specifies that data set in cursor cannot be updated.

SCROLL_LOCKS – specifies that cursor will lock the rows to ensure that updates or deletes made through this will be succeeded.

OPTIMISTIC – specifies that cursor does not lock rows and updates or deletes made through this will not succeed if the row has been updated outside this cursor meanwhile.

[ TYPE_WARNING ]

it specifies that client will receive warning message if any implicit conversion occurs in Cursor.

FOR select_statement

This is standard SELECT statement that defines the result set of the cursor.

[ FOR UPDATE [ OF column_name [ ,…n ] ] ]

It defines modifiable columns within the cursor. If this is specified without a column list, all columns can be updated.


@@Fetch_Status

“Fetch” is used to retrieve rows from Cursor. @@Fetch_Status will be equal to zero for a successful return of row. It can have three values.

0 -Row successfully returned.
-1 -Fetch statement has read beyond the number of rows in Cursor.
-2 -Row no longer exists in result set.

Example


DECLARE employeeID int, employeeBalance double;

DECLARE EmployeeBalanceModificationCursor
CURSOR
LOCAL — [ LOCAL | GLOBAL ]
FORWARD_ONLY — [ FORWARD_ONLY | SCROLL ]
KEYSET — [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
— [ STATIC FAST_FORWARD options are incompatible with FOR UPDATE ]
SCROLL_LOCKS — [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
— [ READ_ONLY is incompatible with FOR UPDATE ]
TYPE_WARNING — [ TYPE_WARNING ]
— [ INFORM client of implicit conversions ]
FOR SELECT employee_ID, employee_Balance
FROM HRMDB.t_EmployeeInformation
ORDER BY employee_ID
FOR UPDATE — [ FOR UPDATE [ OF column_name [ ,…n ] ] ]
;

OPEN EmployeeBalanceModificationCursor; –[ OPEN CURSOR ]

FETCH NEXT FROM EmployeeBalanceModificationCursor –[ FETCH First row ]
INTO @employeeID, @employeeBalance ;

WHILE @@FETCH_STATUS = 0
BEGIN

UPDATE HRMDB.t_EmployeeInformation — [ Change Balance of current employee ]
SET employeeBalance = @employeeBalance + 5000
WHERE CURRENT OF EmployeeBalanceModificationCursor;

FETCH NEXT FROM EmployeeBalanceModificationCursor –[ FETCH Next row ]
INTO @employeeID, @employeeBalance ;

END

CLOSE EmployeeBalanceModificationCursor; — [ CLOSE Cursor]
DEALLOCATE EmployeeBalanceModificationCursor; –[ DEALLOCATE Cursor]


This is very simple example of Cursor; it is not the correct choice for this kind of scenario. Cursor is only advisable for some dynamic operation that can’t be done with other available alternatives.

Thanks
A Rahim Khan

Advertisements
    • Tharindu Dhaneenja
    • November 4th, 2010

    Hi Rahim Khan,
    I just read your post it’s very helpful. I have one small doubt, you mean cursor type when we not specify the default CURSOR type is KEYSET? or it’s DYNAMIC?(I’m not mistaken it’s DYNAMIC)

    Thnaks
    Tharindu Dhaneenja

  1. No trackbacks yet.

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

%d bloggers like this: