Cursors and its usage.


The best way to use Cursors.Many Programmers think Cursors are very inefficient and overhead of sql performance. Because cursors use of pointers and misusage of pointer will make the whole Program destruction and Fetch_status may or may not return a correct value so how to eliminate these things and write a cursor.I want to discuss in this article....

In Sql-Server whenever you are doing batch Programming on Sql-server and retrive row by row data of Result. Many Programmers think using a while loop will give a fruitful result than Cursors.But many people do not try a Mixture of these two things may yield more good result to you .This is the one i want to discuss an article

Normally in every book and an article you read Cursors are the more inefficient and Overhead for Sql-Performance.Yes some times its true.But not in all the Cases.Especially when you are fetching the Records row by row and moves Forward direction to fetch them.
Cursors Optimizes the Performance and its very effective than while loop.I want to add some more Logic to this and make use of Cursors more effective and error free
Look at the General Syntax of Cursors


DECLARE cursor_name CURSOR
LOCAL | GLOBAL
FORWARD_ONLY | SCROLL
STATIC | KEYSET | DYNAMIC | FAST_FORWARD
READ_ONLY | SCROLL_LOCKS | OPTIMISTIC
TYPE_WARNING
FOR select_statement
FOR UPDATE OF column_name ,...n

FETCH NEXT FROM authors_cursor
WHILE @@FETCH_STATUS = 0


Now you see the first statement is Declaring a Cursors.you have several options fastForward option is used to move the Cursor in forward direction and it is the best effective way to optimize the Performance see the next Statement FetchStatus which is a Predefined many people will worried of one thing where Fetch_Status will return a Correct Value or Not.Actually this thing can be eliminated by introducing a while loop.

Look at how i used them in my Programming...

I want to demonstrate by using Update and Select statement

Update Statement


select @UpdatedRowCount =count(Rowid)
from #TempViewRecordset
DECLARE UpdateRow_cursor CURSOR FOR
FAST_FORWARD
SELECT Rowid from #TempViewRecordset
OPEN UpdateRow_cursor
while @UpdatedRowCount> 0
Begin

FETCH NEXT FROM UpdateRow_cursor INTO @UpdateRowids
update #TempViewRecordset
set lineid =@UpdatedRowid
where Rowid = @UpdateRowids

set @UpdatedRowid =@UpdatedRowid + 1
set @UpdatedRowCount=@UpdatedRowCount - 1

End

CLOSE UpdateRow_cursor
DEALLOCATE UpdateRow_cursor

--- Select statement ----
select @Holdviewcount = count(*)
from HoldView where Contactid=@Contactid
DECLARE Hold_cursor CURSOR FOR
FAST_FORWARD

SELECT Hold,Seasonid,
CreatedDate from HoldView
WHERE ContactId= @Contactid

OPEN Hold_cursor

while @Holdviewcount>0
Begin

FETCH NEXT FROM Hold_cursor INTO @ContacidHold,
@SeasonidHold,@TransactionDatesHold

insert into #TEMPCreditorViewreds (SeasonId,
Hold,Credit,Debit,ContactId,TransactionDate)
values (@SeasonidHold,
@ContacidHold,
0,
0,
@Contactid,
@TransactionDatesHold)

set @HoldViewCount =@HoldViewCount - 1

End
CLOSE Hold_cursor
DEALLOCATE Hold_cursor



This is how sql server Cursors are used for best Programming.....


Comments

Author: Vinod Kumar Sahu10 Feb 2013 Member Level: Silver   Points : 0

hi srpbhushan.k,

it was a nice article of cursors with while loop. Thanks for your useful resource



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: