Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Cursor in SQl Server Expand / Collapse
Author
Message
Posted Sunday, March 9, 2008 10:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 12, 2010 11:09 PM
Points: 15, 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

Post #466467
Posted Sunday, March 9, 2008 10:46 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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."
Post #466473
Posted Sunday, March 9, 2008 10:54 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 23, 2009 3:34 AM
Points: 103, 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.
Post #466475
Posted Monday, March 10, 2008 5:44 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 36,995, Visits: 31,517
Ok... once you learn how to use a Cursor, THEN avoid them as if they were Leprosy ;) 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #466583
Posted Monday, March 10, 2008 10:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 30, 2014 5:24 PM
Points: 7,139, Visits: 15,191
Jeff Moden (3/10/2008)
Ok... once you learn how to use a Cursor, THEN avoid them as if they were Leprosy ;) 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?
Post #466822
Posted Monday, March 10, 2008 2:50 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 25, 2010 6:09 AM
Points: 1,621, 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



Post #466964
Posted Monday, March 10, 2008 3:18 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 10:01 AM
Points: 3,844, Visits: 3,841
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
Post #466992
Posted Monday, March 10, 2008 3:26 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:04 PM
Points: 23,302, Visits: 32,057
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.






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)
Post #466998
Posted Monday, March 10, 2008 4:43 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #467025
Posted Monday, March 10, 2008 5:55 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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."
Post #467054
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse