Cursors in SQL Server 2005

Cursors in SQL server are very usefull for updating table.using row by row operations.cursors are like loop in other programming language. we can create cursors very easily.

I will show the scenario where cursors are usefull.If we have have a test_cursors table where we have only districtnames and we want to be update the DistrictId columns with respective to the districtname.and we have district master table where we have correspond Districtname and districtId columns.Suppose we have 100 Different Districtnames for updating ,then we want to write 100 update statements.Instead of check district id and update DistrictId for the proper district we can use Cursors.
Instead of writing multiple statements cursors allows you to update total records sets using fetching rows and updating each row.

There are four basic steps for creating cursors.

First we need to declare cursor.

Declare Cursor(cursorname).

Then after declaring cursor then

open the Cursor(cursorname).

Third step is Fetching the cursor to getting the data.

Fetch cursor(cursor)



begin


declare @dist varchar(50);

declare @distcode int

declare c_master cursor local fast_forward for

select distinct Districtname from dbo.test_cursors

open c_master

fetch c_master into @dist

while(@@fetch_status!=-1)

begin

select @distcode=(select districtid from masterdata.hihlmain.district where Districtname=@dist)

update dbo.test_cursors set Districtid=@distcode where Districtname=@dist

fetch c_master into @dist

end

close c_master

deallocate c_master

end