Static Cursor in SQL Server 2012

Cursor 

In SQL server, a cursor is used when you need to fetch one row at a time or row by row. In this article we see how to work with a cursor. Instead of the T-SQL commands that operate on all the rows in the result set one at a time, we use a cursor when we need to update records in a database table in a singleton fashion, in other words row by row. So let's have a look at a practical example of how to use s Static Cursor in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

Working with cursors consists of several steps:  

1. Declare - Declare is used to define a new cursor.
2. Open - A Cursor is opened and populated by executing the SQL statement defined by the cursor.
3. Fetch - When the cursor is opened, rows can be retrieved from the cursor one by one.
4. Close - After data operations, we should close the cursor explicitly.
5. Deallocate - Finally, we need to delete the cursor definition and release all the system resources associated with the 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 ] ] ]
[;]

Creating the Table in SQL Server

CREATETABLE[dbo].[UserDetail](

            [User_Id][int]NOTNULL,

            [FirstName][varchar](255)NULL,

            [LastName][varchar](255)NOT NULL,

            [Address][varchar](255)NULL

)ON[PRIMARY]

Now insert data into the table. The table will look as in the following:

Static Cursor

A static cursor always displays the result set as it was when the cursor was opened. A static cursor can move in both forward and backward directions. A static cursor is slower and uses more memory in comparison to other cursors. Hence you should use it only if scrolling is required and other types of cursors are not suitable. You cannot update or delete data using a static cursor.

The following is an example of how to obtain a static cursor by using SQL Server:

DECLARE UserDetail_Cursor CURSOR

 STATIC

 FOR

    SELECT User_ID, FirstName, LastName

     FROM UserDetail

     ORDER BY User_ID

 OPEN UserDetail_Cursor

 FETCH NEXT FROM UserDetail_Cursor

 WHILE @@FETCH_STATUS = 0

     FETCH NEXT FROM UserDetail_Cursor

 CLOSE UserDetail_Cursor

 DEALLOCATE UserDetail_Cursor

Output