Cursor in SQL Server


Today we are going to learn about cursors. In this article I describe cursors, declaration of cursors, opening of cursors, fetching from cursors, closing of cursors and deallocating cursors.


A Cursor is a SQL Object, or we can say like a Virtual table that retrieves data from the table one row at a time. We use cursors when we need to update records in a database table in singleton fashion in other words row by row.

Life Cycle of cursor:

  1. Declare
  2. Open
  3. Fetch
  4. Close
  5. Deallocate
Before using a cursor, you first must declare the cursor. Once a cursor has been declared, you can open it and fetch from it. You can fetch row by row and make multiple operations on the currently active row in the cursor. When you have finished working with a cursor, you should close the cursor and deallocate it to release SQL Server resources.

Type of cursor:

  1. Forward Only Cursor
  2. Scroll Cursor
  3. Static Cursor
  4. Dynamic Cursor
  5. Keyset Driven Cursor

Forward only and Scroll Cursors are important So I explain them in detail. First of all we create a table for applying our Cursor.

Creation of table

First we create a table named emp to apply a cursor on this table:

create table emp(emp_id int,em_name varchar(10))

Insertion of data in table

After the creation we insert data as follows:

insert into emp

select 1,'d'union all

select 2,'deepak'union all

select 3,'gourav'union all

select 4,'mahi'union all

select 5,'gill'union all

select 6,'singh'


We will see the table as:

select * from emp



Forward Only cursor

This type of Cursor fetches the next record only. In this type of cursor we can fetch only next record we cant fetch first, last and a spesific record.

Declaration of Forward Only cursor:

We declare a forward-only Cursor as in the following:

declare curscr  cursor


select * from emp

Opening the forward-only Cursor

We open a Forward Only Cursor as in the following:


Fetching data from a Forward Only cursor

We can fetch only next data from the Forward Only Cursor as in the following:

fetch next from curscr

If we execute this query three times than we get the following output:

forward-cursor-in sql.jpg



Closing the Forward Only Cursor

We close the Forward Only Cursor as in the following:

close curscr

Dealoting the Forward Only Cursor :

We delete a Forward Only Cursor as in the following:

deallocate curscr

Scroll Cursor:

We can fetch any record as first, last, prior and specific record from the table.

Declaration of Scroll cursor:

We declare a Scroll Cursor as in the following:

declare scroll_cursor cursor
* from emp

Opening the Scroll Cursor:

We open a Scroll Cursor as in the infollowing:

open scroll_cursor

Fetching data from Scroll cursor

Fetching the first data

fetch first from scroll_cursor


Fetching the next data

fetch last from scroll_cursor



Fetching previous data

fetch prior from scroll_cursor



Fetching Last data

fetch last from scroll_cursor



Fetching absolute data

This fetches the specific data giving absolute position:

fetch absolut 4 from scroll_cursor



Fetching relative data

fetch relative 2 from scroll_cursor



Closing the Scroll Cursor

We close Scroll Cursor as in the following

close scroll_cursor

Dealoting the Scroll Cursor :

We delete Scroll Cursor as following

deallocate scroll_cursor


In this article I described Cursors. I hope this article has helped you in understanding this topic. Please share it. If you know more about this, your feedback and constructive contributions are welcome.