Please enable Javascript to correctly display the contents on Dot Net Tricks!

SQL Server Basics of Cursors

  Author : Shailendra Chauhan
Posted On : 05 May 2012
Total Views : 229,124   
Updated On : 27 Oct 2016
 

Cursor is a database object 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 released all the system resources associated with the cursor.

Syntax to Declare Cursor

Declare Cursor SQL Command is used to define the cursor with many options that impact the scalability and loading behavior 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 

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 

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] 

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 

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 

SQL SERVER – Simple 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 
 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 basic of Cursor in SQL Server with a simple example. I hope after reading this article you will be able to understand cursors in Sql Server. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.



Free Interview Books
 
COMMENTS (2)
Arun Reply

This is the much easier way to understand the concept of Cursors and their usage in SQL.

sreekanth vangara Reply

Nice article, easy to understand for beginers

11 MAR
ASP.NET MVC with Angular2 Development (Online)

09:00 PM - 11:00 PM IST( TUS, THR, SAT)

Know More
6 MAR
Master Class Fast Track MVC 5 with Angular2 Development (Online)

10:30 AM -03:30 PM IST (Fast Track)

Know More
6 MAR
Angular 2 Master Class Fast Track Online Development Training (Online)

10:30 AM- 01:30 PM IST

Know More
27 FEB
ASP.NET MVC with Angular2 Development (Online)

07:00 AM - 09:00 AM IST( MON, WED, FRI)

Know More
25 FEB
ASP.NET MVC with Angular2 Development (Classroom)

05:00 PM-06:30 PM

Know More
25 FEB
Xamarin Forms : Build Cross-platform Apps (Classroom)

3:00 PM-4:30 PM IST

Know More
25 FEB
ASP.NET MVC with Angular2 Development (Classroom)

3:00 PM-4:30 PM

Know More
14 FEB
.NET Development (Classroom)

11:00 AM-12:00 PM

31 JAN
ASP.NET MVC with Angular2 Development (Online)

07:00 AM-09:00 AM IST(Tuesday & Thursday)

30 JAN
NODEJS & MEAN Stack 2.x Development (Online)

09:00 PM-11:00 PM IST( MON, WED, FRI)

15 JAN
PPC Marketing (Classroom)

04:00 PM-05:30 PM

10 JAN
ASP.NET MVC with AngularJS Development (Online)

09:00 PM-11:00 PM IST on (TUE, THRU,SAT)

31 DEC
ASP.NET MVC with AngularJS Development (Classroom)

09:30 AM-11:00 AM

5 NOV
ASP.NET MVC with AngularJS Development (Classroom)

08:00 AM-09:30 AM

BROWSE BY CATEGORY
 
 
LIKE US ON FACEBOOK
 

Professional Speaks

+