Cursor in MS SQL

Cursor is a database objects to retrieve data from a result set one row at a time, instead of the T-SQL commands that operate on all the rows in the result set at one time. We use cursor when we need to update records in a database table in singleton fashion means row by row.

Life Cycle of Cursor
1. Declare Cursor : A cursor is declared by defining the SQL statement that returns a result set.

Syntax to Declare Cursor
Declare Cursor SQL Command is used to define the cursor with many options that impact the scalablity and loading behaviour of the cursor. The basic syntax is given below

DECLARE cursor_name CURSOR
[LOCAL | GLOBAL] --define cursor scope
[FORWARD_ONLY | SCROLL] --define cursor movements (forward/backward)
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD] --basic type of cursor
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] --define locks
FOR select_statement --define SQL Select statement
FOR UPDATE [col1,col2,...coln] --define columns that need to be updated

2. Open Cursor : A Cursor is opened and populated by executing the SQL statement defined by the cursor.

Syntax to Open Cursor
A Cursor can be opened locally or globally. By default it is opened locally. The basic syntax to open cursor is given below:
OPEN [GLOBAL] cursor_name --by default it is local

3. Fetch Data : When cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.

Syntax to Fetch Cursor
Fetch statement provides the many options to retrieve the rows from the cursor. NEXT is the default option. The basic syntax to fetch cursor is given below:
FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n]
FROM [GLOBAL] cursor_name
INTO @Variable_name[1,2,..n]

4. Close Cursor : After data manipulation, we should close the cursor explicitly.

Syntax to Close Cursor
Close statement closed the cursor explicitly. The basic syntax to close cursor is given below:
CLOSE cursor_name--after closing it can be reopen

5. Deallocate : Finally, we need to delete the cursor definition and released all the system resources associated with the cursor.

Syntax to Deallocate Cursor
Deallocate statement delete the cursor definition and free all the system resources associated with the cursor. The basic syntax to close cursor is given below:
DEALLOCATE cursor_name--after deallocation it can't be reopen

Simple Examples of Cursors

First of all create table using below query then insert some record in it.
CREATE TABLE Employee
(
EmpID int PRIMARY KEY,
EmpName varchar (50) NOT NULL,
Salary int NOT NULL,
Address varchar (200) NOT NULL,
)

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,'Deep',28000,'Gurgaon')

Now we will write query for creating cursor as below:

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


I hope this article will help you.