Cursor in SQL Server
This article summarise the use of cursor in T-SQL Statments. I have explained the use of single Cursor and Nester Cursor (Nested Level is 2).
While Loop can also be used for this purpose. I have explained the use of While loop in my previous article Nested Looping in SQL Server.
Cursor Usage and Definition:
Processing of each row in the result set returned in the Select statement. i.e,to do Specific operation on set of Result Set.
Note: While can also be used for this purpose
Syntax:
DECLARE cursor_name CURSOR
FOR Select_statement
Open cursor_name | cursor_variable_name
FETCH NEXT
-- Do Operation
Close cursor_name
Deallocate cursor_name
Explanation:
The usage of Cursor has 6 steps
1. Declaration of cursor
2. Open Cursor
3. Check Fetch Status
4. Do Action on Selected Row
5. Fetch Next
6. Close and Deallocate cursor
Each step is explained in detail in the example.
Consider the Master Data set up for the usage of the While Loop
1. First Level Data Setup:
Create table Student (Studentid int, StudentName varchar(50), StudentStatus varchar(50))
Insert Student values(1,'Vijay','ACTIVE')
Insert Student values(2,'Mahe','INACTIVE')
Insert Student values(3,'Prasanna','ACTIVE')
Select * from Student
Create table Assignment (Assignmentid int, AssignmentName varchar(50))
Insert Assignment values (101, 'English')
Insert Assignment values (102, 'Tamil')
Insert Assignment values (103, 'Maths')
Insert Assignment values (104, 'Science')
Insert Assignment values (105, 'Social')
Create table StudentAssignment (StudentAssignmentid int identity(1,1), Studentid int, Assignmentid varchar(50), AssignmentStatus varchar(50))
2. Modification/Addition in the Master Table
Insert Student values(4,'Abdullah','Active')
Insert Assignment values (106, 'GK')Usage of Cursor:
Req: Select the Student details into Temporary table
Declare @CurStudent cursor
Declare @Studentid int
Declare @StudentName varchar(50)
Create table #tempTable(TempStudentid int, TempStudentName varchar(50) )
--Declaration of cursor
set @CurStudent = cursor for
select Studentid, StudentName from Student
--Open cursor
OPEN @CurStudent
--Fetch the First Record
FETCH NEXT FROM @CurStudent INTO @Studentid, @StudentName
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the operation of the Selected Row
insert #tempTable values(@Studentid, @StudentName )
-- Fetch the Next Record
FETCH NEXT FROM @CurStudent INTO @Studentid, @StudentName
End
select * from #tempTable
drop table #tempTable
--Close and Deallocate the cursor
close @CurStudent
Deallocate @CurStudent
go Usage of Nested Cursor:
Req: Need to create a record for each student for each Assignment in StudentAssignment Table
/******************** Usage of Nested Cursor *********************/
Declare @CurStudent cursor
Declare @CurAssignment cursor
Declare @Studentid int
Declare @Assignmentid int
set @CurStudent = cursor for
select Studentid from Student
OPEN @CurStudent
FETCH NEXT FROM @CurStudent INTO @Studentid
WHILE @@FETCH_STATUS = 0
BEGIN
-- Set Second Level Cursor
set @CurAssignment = cursor for
select Assignmentid from Assignment
OPEN @CurAssignment
FETCH NEXT FROM @CurAssignment INTO @Assignmentid
WHILE @@FETCH_STATUS = 0 -- Fetch status for Inner cursor
BEGIN
if not exists (select StudentAssignmentid from StudentAssignment
where Studentid = @Studentid and Assignmentid = @Assignmentid)
Insert StudentAssignment(Studentid,Assignmentid,AssignmentStatus)
Select @Studentid, @Assignmentid, 'SUBMITTED'
--Next Record
FETCH NEXT FROM @CurAssignment INTO @Assignmentid
End
close @CurAssignment
Deallocate @CurAssignment
FETCH NEXT FROM @CurStudent INTO @Studentid
End
close @CurStudent
Deallocate @CurStudent
Advantages of Cursor
1. We have different Type of cursor(s)like FORWARD_ONLY/ Static/ dynamic,etc. depending upon the requirement we can use them.
2. Usage of cursor or while loop depends on our requirement(s).
Reference(s);
http://www.techrepublic.com/blog/datacenter/comparing-cursor-vs-while-loop-performance-in-sql-server-2008/1741