Cursor In SQL Server

Introduction

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.

Cursor

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

Output

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

for

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:

Output

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

Output

Fetching the next data

fetch last from scroll_cursor

Output

Fetching previous data

fetch prior from scroll_cursor

Output

Fetching Last data

fetch last from scroll_cursor

Output

Fetching absolute data

This fetches the specific data giving absolute position:

fetch absolut 4 from scroll_cursor

Output

Fetching relative data

fetch relative 2 from scroll_cursor

Output

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

Summary

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.