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

  1. insert into emp  
  2. select 1, 'd'  
  3. union all  
  4. select 2, 'deepak'  
  5. union all  
  6. select 3, 'gourav'  
  7. union all  
  8. select 4, 'mahi'  
  9. union all  
  10. select 5, 'gill'  
  11. union all  
  12. 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:

open curscr

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
scroll for
select * 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.

Up Next
    Ebook Download
    View all
    View all