Cursor in SQL Server

Before reading this article visit,Cursor in SQL Server.

Introduction:

In this article I describe Static Cursor, Keyset-Driven Cursor and Dynamic Cursor.

First we are creating a table.

Creation of table:

create table emp(empId int, empName varchar(10))

Insertion of data:

insert into emp
select 1,'d'union all
select 2,'e'union all
select 3,'e'union all
select 4,'p'union all
select 5,'a'union all
select 6,'k'

Output:

select * from emp



Static Cursor:

A Static Cursor shows the result set as it was, when the cursor was opened. If we make change to the table after the opening of Static Cursor then these changes are not reflected in the cursor. For example if after opening the cursor we delete a row, and fetch that record by the cursor, then the cursor shows that record.

Syntax:

Declare cursorName cursor
static for
statement

Here cursorName is the name of your cursor, static is a keyword, and statement is the SQL statement that you are used in the cursor. An example is given below.

Creation of Static Cursor:

declare static_cursor cursor
static for
select * from emp

Opening of Static Cursor:

open static_cursor

Fetching data from Static Cursor:

Fetching first data from Static Cursor:

fetch first from static_cursor

Output:



Fetching next data from Static Cursor:

fetch next from static_cursor

Output:



Fetching last data from Static Cursor:

fetch last from static_cursor

Output:

Fetching second data from Static Cursor:

fetch absolute 2 from static_cursor

Output:



Fetching next 2nd data from Static Cursor:
 

fetch relative 2 from static_cursor

Output:



Closing of Static Cursor:

close static_cursor

Dealloting Static Cursor:

deallocate static_cursor

Dynamic Cursor:

Dynamic Cursor is the opposite of the Static Cursor. When we make changes in the table after opening the Dynamic Cursor and scroll through a Dynamic Cursor all changes made to the table reflects the Dynamic Cursor. For example If we delete the record from the table after the opening of the Dynamic Cursor, we can't access that record.

Syntax:

Declare cursorName cursor
Dynamic for
statement

Here cursorName is the name of your cursor, Dynamic is a keyword, and statement is the SQL statement that you are used in cursor. An example is given below.

Creation of Dynamic Cursor:

declare dynamic_cursor cursor
dynamic for
select * from emp

Opening of Dynamic Cursor:

open dynamic_cursor

Fetching first data from Dynamic Cursor:

fetch First from dynamic_cursor

Output:

Fetching next data from Dynamic Cursor:

fetch next from dynamic_cursor

Output:



We can fetch last, relative and absolute data same as Static Cursor.

Closing of Dynamic Cursor:

close dynamic_cursor

Dealloting  Dynamic Cursor:

deallocate dynamic_cursor

Keyset-Driven Cursor:

When we open keyset-drivin cursor then it creates a list of unique values in the tempdb database. These values are called a keyset. Every keyset uniquely identifies a single row in the result set.

A Keyset is created after the opening of the cursor so the number of rows are fixed until we close the cursor.

Creation of Keyset-drivin cursor:

declare key_cursor cursor
keyset for
select * from emp

Opening of Keyset-drivin cursor:

open key_cursor

Featching data from Keyset-Drivin cursor:

Fetching first data from Keyset-drivin cursor:

fetch First from key_cursor

Output:

Fetching next data from Keyset-drivin cursor:

fetch next from key_cursor

Output:



Closing of Keyset Drivin cursor:

close key_cursor

Dealocation of mamory:

deallocate key_cursor

Summary:

In this article I described Static Cursor, Dynamic Cursor and Keyset-Drivin Cursor. 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.