SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cursor in SQl Server


Cursor in SQl Server

Author
Message
thaya2002
thaya2002
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 44
hi,
I didn't really understand the cursor in SQL server.

how is cursor used in SQL Server?

once we declare cursor,processing,deallocate cursor.

after how can data be fetched to Select statement to display in stored procs.

can anyone give me some tips about it

advanced thanks


ts
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14734 Visits: 9518
By far the most important tip on Cursors is: Avoid them as though they were leprosy.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Nitin Sontakke
Nitin Sontakke
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 29
Although it is true that you should try your best to avoid the cursor, a particular feature in any language exists because it has some usage.

See the DECLARE CURSOR Transact-SQL help in SQL Server Books Online and you will find and detailed example right at the bottom of that article.

Hope it helps.

Nitin.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86039 Visits: 41095
Ok... once you learn how to use a Cursor, THEN avoid them as if they were Leprosy Wink There is very very little you actually need a cursor for in the line of production code and then it needs to be a "firehose" cursor.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12225 Visits: 18574
Jeff Moden (3/10/2008)
Ok... once you learn how to use a Cursor, THEN avoid them as if they were Leprosy Wink There is very very little you actually need a cursor for in the line of production code and then it needs to be a "firehose" cursor.


..in which case you might be better off doing that in a procedural language... Yes I said it.

But first - work at the problem as if Cursors don't exist. There's a lot of power included in set-based processing, and it usually just takes a little "letting go" to get it to work. You don't need to tell SQL server "how" to do something - just tell it "what" to do, and "what" rows you need that done to, and voila - mostly set-based by then. All that extra control is stuff for procedural code - NOT set-based.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Vijaya Kadiyala
Vijaya Kadiyala
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1887 Visits: 409
Hi,
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. For example, you can use cursor to include a list of all user databases and make multiple operations against each database by passing each database name as a variable.

Check out the below to get an idea on How the cursor works.
http://www.databasejournal.com/features/mssql/article.php/1439731

But in real lilfe try to avoid using cursor.
Check out the below to get an idea why you should avoid using cursors
http://www.sql-server-performance.com/tips/cursors_p1.aspx

Please let me know if you have any questions.
Thanks -- Vj
http://dotnetvj.blogspot.com

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers



John Rowan
John Rowan
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6028 Visits: 4551
Not understanding cursors is a benefit to you! Matt has some great advice. Work the problem as if cursors do not exist. If you are going to spend your time trying to understand how cursors work, spend your time instead understanding Set based processing.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39328 Visits: 38535
Cursors may be evil, but they may be a necessary evil. Where possible use set based solutions, but sometimes you may find cursors are necessary, and therefore knowing how to write them and use them can be helpful.

Corrallary (spelling ?), knowing how to write them, can also help you understand existing cursors and how to rewrite them as a set based solution.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86039 Visits: 41095
Problem is... most people give up on trying to find a good set-based solution way too early...

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14734 Visits: 9518
Lynn Pettis (3/10/2008)
Cursors may be evil, but they may be a necessary evil...


In my experience they are necessary and acceptable for only one thing: variable-izing SQL objects in the repeated execution of dynamic SQL or system procedures. In other words: DBA operational utility procedures (and these are not normally written by the uninitiated, nor should they be). And even in this case, there are very often acceptable ways around it.

Although I have heard many other allusions to possible instances where cursors were necessary, not one has materialized and I have begun to regard it as an urban legend.

I would like to keep an open mind on this but until someone can demonstrate a straight-forward example of cursors being necessary (except for the case that I cited above) or even just better than other options, I cannot see the point any longer.

For now, I regard them as an unnecessary evil.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search