---

Search posts under SQL Server:

---

SQL Server > Cursor

In this section, we shall learn about Cursor in SQL Server.

What is Cursor in SQL Server?

Cursor is a dynamically created database object to retrieve data from a result set (built using sql query) in row by row basis.

There are two types of cursor in SQL Server

1. Forward only - default, if not specified. This cursor can only go forward from its starting position
2. Scroll cursor - this cursor can retrieve data with different fetch options like FIRST, LAST, NEXT, PRIOR, RELATIVE, ABSOLUTE. It means this cursor can move foward, come backward, go to specific row or position in the result set

Cursor scope can be declared as

1. Local -  it means this cursor can be used only in the stored procedure or triggers in which it is declared
2. Global - it means this cusor can be used in any of the stored procedure, triggers executed by the same connection

We can also define how the cursor behaves to the data on which it is built, the cursor can be

1. Static - creates a temporary copy of the data to be used by the cursor
2. Dynamic - creates a cursor that reflects the changes made to the data as and when we navigate through them

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] 
     [ FORWARD_ONLY | SCROLL ] 
     [ STATIC | DYNAMIC | FAST_FORWARD ] 
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
     [ TYPE_WARNING ] 
     FOR select_statement 
OPEN cursor_name
FETCH NEXT FROM cursor_name
[;]

Permission of cursor

Permission of declaring cursor is same as SELECT permission of the user.

Steps to create a cursor

1. Declare a cursor
2. Open declared cursor
3. Fetch rows from the result set
4. Close the cursor - releases the current result set and free cursor locks if any. If a cursor is closed, it can be opened again as it's data structure remain in the memory.
5. Deallocate the cursor (Global cursor is deallocated when the database connection is closed from which the cursor was created) - removes the cursor reference. Once deallocated, we can't use this cursor, we will need to declare it again.

Note that closing and deallocating the cursor is very important.

Sql Server Cursor example

Below is a very simple cursor declared.

DECLARE @AutoId int
DECLARE @FullName varchar(100)

-- Step 1
DECLARE MyDetailsCursor CURSOR FOR
SELECT AutoId, FirstName + ' ' + LastName FROM PersonalDetails
WHERE AutoId > 1100;

-- Step 2
OPEN MyDetailsCursor

-- Step 3
FETCH NEXT FROM MyDetailsCursor INTO @AutoId, @FullName

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT CAST(@AutoId as varchar(20))+ ' -> ' + @FullName
    FETCH NEXT FROM MyDetailsCursor INTO @AutoId, @FullName
END

-- Step 4
CLOSE MyDetailsCursor

-- Step 5
DEALLOCATE MyDetailsCursor

This will print data in Message window that looks something like below.

To know different types of cursor, read below posts.

Read posts under SQL Server > Cursor

1. Cursor example
2. Scroll cursor example