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 cursor cursor

for

select * from emp

Opening the forward-only Cursor

We open a Forward Only Cursor as in the following:

open cursor

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 cursor

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 cursor

Dealoting the Forward Only Cursor

We delete a Forward Only Cursor as in the following:

deallocate cursor

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 following:

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 absolute 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

Deallocating 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.

Next Part >> Static Cursor, Dynamic Cursor, and Keyset-Driven Cursor in SQL Server