Static Cursor in SQL Server

SQL 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

CREATE TABLE [dbo].[UserDetail] (
    [User_Id] INT NOT NULL,
    [FirstName] VARCHAR(255) NULL,
    [LastName] VARCHAR(255) NOT NULL,
    [Address] VARCHAR(255) NULL,
    PRIMARY KEY ([User_Id])
);

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