Allows row-by-row processing. Control will be with user rather than SQL server
Different options can be applied while creating a cursor

o Local/global

o Static/dynamic/fast_forward/ketyset driven

o Forward_only/scroll

Generally it should be avoided as it is affecting a performance a big time
If another SP is being called within cursor, it will fail. So while loop should be used instead of cursor
A general syntax

DECLARE cur CURSOR

FOR SELECT a,b FROM TableName

OPEN cur

FETCH NEXT FROM Cur INTO @a, @b

WHILE @@FETCH_STATUS = 0

BEGIN

-- Your logic

FETCH NEXT FROM Cur INTO @a, @b

END

CLOSE cur

DEALLOCATE cur

In some cases, Cursor will be faster than normal join e.g.

when there is some N level of parent-child relationship than rather than joining the same table multiple times, we can use cursor and achieve the results fast
When you have to insert millions of record in one table then probably you can start cursor and insert a bunch of records (let's say 25K records) in chunks which will be faster than a single INSERT statement

Even if we have to use cursor, it is recommended that we use WHILE LOOP rather than using cursor

Keyset-Driven Cursor

Introduction

In this article I describe keyset-driven cursor. With the help of keyset-drivin cursor we can find first, next, last and any specific row of the table.

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

Creation of table

create table emp(emp_id int,em_name varchar(10))
go
insert into emp
select 1,'d' union all
select 2,'e' union all
select 3,'f' union all
select 4,'mahi' union all
select 5,'gill' union all
select 6,'singh'
go
select * from emp

Output:

Keyset-driven cursor:

It lies between a static and a dynamic cursor. When we open keyset-drivin cursor than it saves the list of unique values for the entire result set, these values are called keyset. These keys retrieve the current data values for each row.

Creation of Keyset-drivin cursor:

declare key_cursor cursor
keyset for
select * from emp

Opening of Keyset-drivin cursor:

We open a Keyset-drivin Cursor as in the in following:

open key_cursor

Fetching first data from Keyset-drivin Cursor:

fetch firstfrom key_cursor

Outout:

Closing the Keyset-drivin Cursor:

We close Keyset-drivin Cursor as in the following

close key_cursor

Dealloting Keyset-drivin Cursor:

We deallocate Keyset-drivin Cursor as following

SQL SERVER – Examples of Cursors


 CREATE TABLE Employee
(
 EmpID int PRIMARY KEY,
 EmpName varchar (50) NOT NULL,
 Salary int NOT NULL,
 Address varchar (200) NOT NULL,
)
GO
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(1,'Mohan',12000,'Noida')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(2,'Pavan',25000,'Delhi')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(3,'Amit',22000,'Dehradun')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(4,'Sonu',22000,'Noida')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(5,'Deepak',28000,'Gurgaon')
GO
SELECT * FROM Employee

Static Cursor - Example


 SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
DECLARE @salary int
 DECLARE cur_emp CURSOR
STATIC FOR 
SELECT EmpID,EmpName,Salary from Employee
OPEN cur_emp
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
 WHILE @@Fetch_status = 0
 BEGIN
 PRINT 'ID : '+ convert(varchar(20),@Id)+', Name : '+@name+ ', Salary : '+convert(varchar(20),@salary)
 FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
 END
END
CLOSE cur_emp
DEALLOCATE cur_emp
SET NOCOUNT OFF

Dynamic Cursor - Example


 --Dynamic Cursor for Update
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
 DECLARE Dynamic_cur_empupdate CURSOR
DYNAMIC 
FOR 
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Dynamic_cur_empupdate
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM Dynamic_cur_empupdate INTO @Id,@name
 WHILE @@Fetch_status = 0
 BEGIN
 IF @name='Mohan'
 Update Employee SET Salary=15000 WHERE CURRENT OF Dynamic_cur_empupdate
 FETCH NEXT FROM Dynamic_cur_empupdate INTO @Id,@name
 END
END
CLOSE Dynamic_cur_empupdate
DEALLOCATE Dynamic_cur_empupdate
SET NOCOUNT OFF
 Go
Select * from Employee


 -- Dynamic Cursor for DELETE
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
 DECLARE Dynamic_cur_empdelete CURSOR
DYNAMIC 
FOR 
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Dynamic_cur_empdelete
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM Dynamic_cur_empdelete INTO @Id,@name
 WHILE @@Fetch_status = 0
 BEGIN
 IF @name='Deepak'
 DELETE Employee WHERE CURRENT OF Dynamic_cur_empdelete
 FETCH NEXT FROM Dynamic_cur_empdelete INTO @Id,@name
 END
END
CLOSE Dynamic_cur_empdelete
DEALLOCATE Dynamic_cur_empdelete
SET NOCOUNT OFF
Go
Select * from Employee

Forward Only Cursor - Example


 --Forward Only Cursor for Update
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
 DECLARE Forward_cur_empupdate CURSOR
FORWARD_ONLY
FOR 
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Forward_cur_empupdate
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM Forward_cur_empupdate INTO @Id,@name
 WHILE @@Fetch_status = 0
 BEGIN
 IF @name='Amit'
 Update Employee SET Salary=24000 WHERE CURRENT OF Forward_cur_empupdate
 FETCH NEXT FROM Forward_cur_empupdate INTO @Id,@name
 END
END
CLOSE Forward_cur_empupdate
DEALLOCATE Forward_cur_empupdate
SET NOCOUNT OFF
 Go
Select * from Employee


 -- Forward Only Cursor for Delete
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
 DECLARE Forward_cur_empdelete CURSOR
FORWARD_ONLY
FOR 
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Forward_cur_empdelete
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM Forward_cur_empdelete INTO @Id,@name
 WHILE @@Fetch_status = 0
 BEGIN
 IF @name='Sonu'
 DELETE Employee WHERE CURRENT OF Forward_cur_empdelete
 FETCH NEXT FROM Forward_cur_empdelete INTO @Id,@name
 END
END
CLOSE Forward_cur_empdelete
DEALLOCATE Forward_cur_empdelete
SET NOCOUNT OFF
 Go
Select * from Employee