| Author |
Topic |
|
bala17582
Starting Member
5 Posts |
 Posted - 2004-09-21 : 15:31:43
|
Hi guys.......Could u please tell me wat a cursor is? when and how to use cursors.......in a simple defenitions...... Better Late Than Never Bala |
|
|
gpl
Posting Yak Master
195 Posts |
Posted - 2004-09-21 : 16:18:37
|
| It is the spawn of the devil and shouldnt be usedok, so you have sold your soul, it is a way of processing a set of records sequentially, you define a rowset and in a loop, iterate through them, doing whatever it is you want to do, although less efficiently than a set operation.There are a few circumstances where only a cursor will do but they are few and far between.Graham |
 |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-09-21 : 16:23:58
|
quote:
Originally posted by gplThere are a few circumstances where only a cursor will do but they are few and far between.
I'm not even sure I would use cursors when there is a reason to do things sequentially... I like a while loop of some sort just because there isn't the overhead of the cursor.In fact, I still really don't even know how to write a cursor...Corey |
|
|
|
gpl
Posting Yak Master
195 Posts |
Posted - 2004-09-21 : 17:49:05
|
quote:
Originally posted by Seventhnight
quote:
Originally posted by gplThere are a few circumstances where only a cursor will do but they are few and far between.
I'm not even sure I would use cursors when there is a reason to do things sequentially... I like a while loop of some sort just because there isn't the overhead of the cursor.In fact, I still really don't even know how to write a cursor...Corey
There you are, over 750 posts and he wouldnt touch them, just dont do it |
|
|
|
sabirpatel
Starting Member
22 Posts |
Posted - 2004-09-22 : 04:23:49
|
| CUrsor is just like a recordset. It will fetch a set of data and allocate a seperate memory space. Then you can fetch each row one by one and do the operations. YOu can refer the TSQL help for a complete discription of it. |
|
|
|
slacker
Posting Yak Master
115 Posts |
Posted - 2004-09-22 : 07:07:42
|
quote:
Originally posted by Seventhnight
[quote]Originally posted by gpl I like a while loop of some sort just because there isn't the overhead of the cursor.
I have seen an example of this before but I dont think I could get it to work. How do you write a while loop to handle a result set in sql without using a cursor? |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-09-22 : 07:28:50
|
Well it depends on what it is, but a general example would be:while exists(Select * From #processTable)Begin Select top 1 @curId = id From #processTable do calcs... Delete From #processTable Where id = @curIdEnd EDIT: I still say it should be set-based if at all possibleCorey |
|
|
|
slacker
Posting Yak Master
115 Posts |
Posted - 2004-09-22 : 07:33:51
|
quote:
EDIT: I still say it should be set-based if at all possibleCorey
Of course... Thats always the idea.. get it in one slick sql statement :p. Usually I find when I absolutely need a cursor that its something that isnt going to be hit up all the time. So I dont worry about performance. |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-09-22 : 08:20:38
|
I always worry about performance... My development server is dual 400mhz or some such  and maybe a gig of RAM. If it my code runs quickly on it, then its good to go!!  Corey |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-22 : 13:00:47
|
| "Could u please tell me wat a cursor is?when and how to use cursors......."I don't know the answer to this question, however if the question had been"Could u please tell me how NOT to use cursors......."I would have know the answer ...Kristen |
|
|
|
bala17582
Starting Member
5 Posts |
Posted - 2004-09-22 : 18:38:54
|
is that #processTable a temporary table .....
should we deallocate the #table or should we delete the table at the end of the transaction....and wat is the difference between them.Better Late Than Never Bala |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-09-22 : 18:41:40
|
| if you are running it in a stored procedure it is dropped when the sp completes...if running it in QA then drop it...It can last only as long as that particular connection lasts.Corey |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-22 : 18:43:56
|
| Homework questions?Tara |
|
|
|
bala17582
Starting Member
5 Posts |
Posted - 2004-09-22 : 18:59:23
|
| after I create a #temptable with 8 fields, I select 7 fields from a normal table.. and I open the cursor.... then when i insert 8 values into the #table its throwing this error......."Cursorfetch: The number of variables declared in the INTO list must match that of selected columns."Better Late Than Never Bala |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-22 : 19:32:19
|
| Too funny. I guess you missed the part about not using cursors.Tara |
|
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2004-09-22 : 20:10:21
|
| I really like the While loop that was great!!! Maybe the SQL TEAM can make this a topic for the front page... Done this a thousand times in Application code Great to finally see a simple example of using both temp tables, variables, NO CURSOR!!!Surf On Dude! |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-09-22 : 21:47:23
|
quote:
Originally posted by tduggan
Too funny. I guess you missed the part about not using cursors.Tara
Yeah... I decided If he was going to ignore our suggestions why give them...  Corey |
|
|
|
GunZ
Starting Member
29 Posts |
Posted - 2004-09-22 : 22:54:09
|
[humor]A cursor is used for cursing elements of the record set[/humor]Imagine a record set returned by a SELECT statement. Now imagine processing every single row of that record set. To keep track of what is "the current record", you curse(?) it with a cursor. How to use a cursor... look it up in Books OnlinePS: My boss is away today so I can afford this gibberish  Australia.NSW.Sydney.GunZ |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-09-22 : 22:57:02
|
| This just gripes me. I work 1900 hours a day and help out on forums when I have time (which isn't much lately). I don't mind helping people, but people who are too stupid to use the best resource tool ever made (that would be Books Online) when they just need a simple example really bug me. How about looking in the templates section of Query Analyzer?????I don't suppose you installed the free version of SQL Server when you bought your textbook did you?Sorry....hope I didn't offend anyone. It's been a long month. :)</RANT>MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
GunZ
Starting Member
29 Posts |
Posted - 2004-09-22 : 23:42:45
|
quote:
Originally posted by derrickleggett
... use the best resource tool ever made (that would be Books Online)
Not the best sir, the best is yet to come. The Books Online help could use some more help.But yeah... it can dismay some people when they overlook something obvious. I just took the opportunity to make some humor about it to lighten as I'm in a state of confusion in the twilight zone while my team leader is away.Australia.NSW.Sydney.GunZ |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-09-23 : 06:52:19
|
| what is this best yet to come... i learned probably 90% of the SQL I know from BOL. Another 7% from google, and i guess that leaves 3% from here...Corey |
|
|
|
Next Page
|
Could Any one tell wat is a Cursor and how to use