The syntax of SQL Server Cursor

Please visit my new Web Site WWW.Codedisplay.com

Cursor means memory address where SQL data processed. By using cursor one can process data one by one by looping through all records within a cursor. So when developer thinks that there is no way to accomplish an operation by writing a single query then the alternative solution is cursor. That means cursor ease our life by providing a looping mechanism through all records. But one thing keep in mind that SQL Server cursor performance is very bad than oracle cursor. So think twice when you want to write a cursor. In my next article i will show you how one can avoid cursor by using simple while loop statement. This is not the focus area of my current Article. Better I will discuss very basics on SQL Server Cursor. One thing is clear that when we need to process row one by one or if we cant built a logic by a single SQL statement then we will use SQL Server cursor. Steps to follow to write a cursor in SQL Server:

1. Declare cursor
2. Open cursor
3. Fetch row from the cursor
4. Process fetched row
5. Close cursor
6. Deallocate cursor

Before starting to write a real life example it will be better to discuss the syntax of SQL Server cursor.

Syntax:

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 ] ] ]

Description:
cursor_name is the name of the Transact-SQL server cursor defined. cursor_name must conform to the rules for identifiers.

LOCAL specifies that the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created. The cursor name is only valid within this scope. The cursor can be referenced by local cursor variables in the batch, stored procedure, or trigger, or a stored procedure OUTPUT parameter. An OUTPUT parameter is used to pass the local cursor back to the calling batch, stored procedure, or trigger, which can assign the parameter to a cursor variable to reference the cursor after the stored procedure terminates. The cursor is implicitly deallocated when the batch, stored procedure, or trigger terminates, unless the cursor was passed back in an OUTPUT parameter. If it is passed back in an OUTPUT parameter, the cursor is deallocated when the last variable referencing it is deallocated or goes out of scope.

GLOBAL specifies that the scope of the cursor is global to the connection. The cursor name can be referenced in any stored procedure or batch executed by the connection. The cursor is only implicitly deallocated at disconnect.

Note If neither GLOBAL or LOCAL is specified, the default is controlled by the setting of the default to local cursor database option. In SQL Server version 7.0, this option defaults to FALSE to match earlier versions of SQL Server, in which all cursors were global. The default of this option may change in future versions of SQL Server.

FORWARD_ONLY specifies that the cursor can only be scrolled from the first to the last row. FETCH NEXT is the only supported fetch option. If FORWARD_ONLY is specified without the STATIC, KEYSET, or DYNAMIC keywords, the cursor operates as a DYNAMIC cursor. When neither FORWARD_ONLY nor SCROLL is specified, FORWARD_ONLY is the default, unless the keywords STATIC, KEYSET, or DYNAMIC are specified. STATIC, KEYSET, and DYNAMIC cursors default to SCROLL. Unlike database APIs such as ODBC and ADO, FORWARD_ONLY is supported with STATIC, KEYSET, and DYNAMIC Transact-SQL cursors. FAST_FORWARD and FORWARD_ONLY are mutually exclusive; if one is specified the other cannot be specified.

STATIC defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications.

KEYSET specifies that the membership and order of rows in the cursor are fixed when the cursor is opened. The set of keys that uniquely identify the rows is built into a table in tempdb known as the keyset. Changes to nonkey values in the base tables, either made by the cursor owner or committed by other users, are visible as the owner scrolls around the cursor. Inserts made by other users are not visible (inserts cannot be made through a Transact-SQL server cursor). If a row is deleted, an attempt to fetch the row returns an @@FETCH_STATUS of -2. Updates of key values from outside the cursor resemble a delete of the old row followed by an insert of the new row. The row with the new values is not visible, and attempts to fetch the row with the old values return an @@FETCH_STATUS of -2. The new values are visible if the update is done through the cursor by specifying the WHERE CURRENT OF clause.

DYNAMIC defines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor. The data values, order, and membership of the rows can change on each fetch. The ABSOLUTE fetch option is not supported with dynamic cursors.

FAST_FORWARD specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified. FAST_FORWARD and FORWARD_ONLY are mutually exclusive; if one is specified the other cannot be specified.

READ_ONLY prevents updates made through this cursor. The cursor cannot be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. This option overrides the default capability of a cursor to be updated.

SCROLL_LOCKS specifies that positioned updates or deletes made through the cursor are guaranteed to succeed. Microsoft® SQL Server™ locks the rows as they are read into the cursor to ensure their availability for later modifications. SCROLL_LOCKS cannot be specified if FAST_FORWARD is also specified.

OPTIMISTIC specifies that positioned updates or deletes made through the cursor do not succeed if the row has been updated since it was read into the cursor. SQL Server does not lock rows as they are read into the cursor. It instead uses comparisons of timestamp column values, or a checksum value if the table has no timestamp column, to determine whether the row was modified after it was read into the cursor. If the row was modified, the attempted positioned update or delete fails. OPTIMISTIC cannot be specified if FAST_FORWARD is also specified.

TYPE_WARNING specifies that a warning message is sent to the client if the cursor is implicitly converted from the requested type to another.

select_statement is a standard SELECT statement that defines the result set of the cursor. The keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO are not allowed within select_statement of a cursor declaration.

SQL Server implicitly converts the cursor to another type if clauses in select_statement conflict with the functionality of the requested cursor type.

FOR UPDATE [OF column_name [,...n]] defines updatable columns within the cursor. If OF column_name [,...n] is supplied, only the columns listed allow modifications. If UPDATE is specified without a column list, all columns can be updated, unless the READ_ONLY concurrency option was specified.

EXAMPLE:

DECLARE @Slab1From INT
DECLARE @Slab2From INT
DECLARE @Slab3From INT
DECLARE Slab_cursor CURSOR FOR
SELECT CONVERT(INT,SUBSTRING(Slab1A_Time_From,1,2)),
CONVERT(INT,SUBSTRING(Slab2A_Time_From,1,2)),
CONVERT(INT,SUBSTRING(Slab3A_Time_From,1,2))
FROM tariff_table_test

OPEN Slab_cursor
FETCH NEXT FROM Slab_cursor INTO @Slab1From,@Slab2From,@Slab3From

WHILE @@FETCH_STATUS = 0
BEGIN
IF @Slab1From>@Slab2From
BEGIN
Print 'Slab1 is greater than Slab2'
-- Other logic here
END

IF @Slab1From>@Slab3From
BEGIN
Print 'Slab1 is greater than Slab3'
--Other logic here
END

FETCH NEXT FROM Slab_cursor INTO @Slab1From,@Slab2From,@Slab3From
END

CLOSE Slab_cursor
DEALLOCATE Slab_cursor

Indications:
Steps to write SQL Server that i have discussed at the beginning of this article is given below:


Code Explanation:
In the above example here i need to apply some business rules when slab1 is greater than slab2 or slab3. SUBSTRING is used since my data was in different format like 08:17:59. And i convert it into INT since I need to compare with Slab2 & Slab3. Here what i did actually doesn't a matter. The matter is how one can write SQL Server Cursor. And hope now you can write SQL Server cursor from simple to complex one.

Remarks:
If a DECLARE CURSOR using Transact-SQL syntax does not specify READ_ONLY, OPTIMISTIC, or SCROLL_LOCKS, the default is as follows:

If the SELECT statement does not support updates (insufficient permissions, accessing remote tables that do not support updates, and so on), the cursor is READ_ONLY.

STATIC and FAST_FORWARD cursors default to READ_ONLY.

DYNAMIC and KEYSET cursors default to OPTIMISTIC.

Permissions:
DECLARE CURSOR permissions default to any user that has SELECT permissions on the views, tables, and columns used in the cursor.

This is an Introduction on how to Write SQL Server Cursor. For better understanding you can read below links.

REF:
http://msdn.microsoft.com/en-us/library/ms180169.aspx
http://www.teratrax.com/articles/sql_server_cursor.html