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.

2. Open

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

3. Fetch

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

4. Close

   After data manipulation, we should close the cursor explicitly.

5. Deallocate

   Finally, we need to delete the cursor definition and release all the system resources associated with the cursor.

Syntax to declare Cursor

Daclare Cursor SQL command is used to define the cursor with many options that impact the flexibility and locking behavior of the cursor. The basic synatx is as 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 select statement
FOR UPDATE OF [col1,col2......coln] --define columns that need to be updated 

Syntax to open Cursor

A Cursor can be opened locally or globally. By default it is opened locally. The basic synatx is as given below:

 OPEN [GLOBAL] cursor_name --by default is local 

Syntax to fetch Cursor

Fetch statement provides the many options to navigate the rows in the cursor. The basic synatx is as given below:

 FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n]
FROM [GLOBAL] cursor_name
INTO [@var1,@var2...@varn] 

Syntax to close Cursor

A Cursor should be closed after using it. The basic synatx is as given below:

 CLOSE [GLOBAL] cursor_name 

Syntax to Deallocate Cursor

Finally, a Cursor should be deallocated in order to release all the system resources associated with the cursor. The basic synatx is as given below:

 DEALLOCATE [GLOBAL] cursor_name 

SQL SERVER – Simple Example of Cursor

 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 

 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 

Summary

In this article I try to explain the Cursor in SQL Server with a simple example. I hope after reading this article you will be able to understand Cursor in Sql Server. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.