What is Cursor in Sql Server

Cursor in Sql Server

It is temporary memory space which is used to read data from one by one records from result set

• Row by row read
• It is slow process
• When while loop doesn’t work than cursor uses
• It is part of program like store procedure or function

Type of Cursor

• Static
• Dynamic
• Forward- Only
• KeySet

Life Cycle of Cursor…………………………………………..

• Declare Cursor A cursor is declared by defining the SQL statement that returns a result set.
• Open A Cursor is opened and populated by executing the SQL statement defined by the cursor.
• Fetch When cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.
• Close After data manipulation, we should close the cursor explicitly.
• Deallocate Finally, we need to delete the cursor definition and released all the system resources associated with the cursor.

For Example …………………………………

Note: In this topic we will discuss about how to read Rollno record from table emp row by row with using cursor c1

-- create table emp.................

create table emp(SrNo int ,Name varchar(50),Salary int)

--query for insert

insert into emp values(1,'Kush Tiwari',8900)

insert into emp values(2,'Ravinder Goyel',90000)

--create procedures which name is pr_GetSrNowithCursor with Cursor...............

create proc pr_GetSrNowithCursor

as

begin

declare @SrNo int

declare c1 cursor for select SrNo from emp

open c1

fetch next from c1 into @SrNo

while @@FETCH_STATUS=0

begin

print 'RollNo is  '+ convert(varchar(20),@SrNo)

fetch next from c1 into @SrNo

end

close c1

deallocate c1

end

-- exec Procedures to check

exec pr_GetSrNowithCursor

Result