| Author |
Topic |
|
subhaoviya
Posting Yak Master
135 Posts |
 Posted - 2011-10-15 : 08:10:47
|
| Hi friends, my knowledge is about cursor it that it is used for row by row iteration. we can avoid it by using while looping based on index column. can anyone explain the real situation that we go for using cursor in 2008 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-15 : 12:49:47
|
| One practical scenario where you use cursors is when you want to execute a stored procedure passing values from rows of a table and you want to retrieve the results for each row of the table.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-10-15 : 13:14:07
|
quote:
Originally posted by visakh16
One practical scenario where you use cursors is when you want to execute a stored procedure passing values from rows of a table and you want to retrieve the results for each row of the table.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
I think with table valued parameters there is no need to use a cursor in the above scenario.PBUH |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-16 : 00:05:55
|
quote:
Originally posted by Sachin.Nand
quote:
Originally posted by visakh16
One practical scenario where you use cursors is when you want to execute a stored procedure passing values from rows of a table and you want to retrieve the results for each row of the table.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
I think with table valued parameters there is no need to use a cursor in the above scenario.PBUH
yeah thats truefrom sql 2008 onwards you've that featureTill that time we use to implement it using cursor.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-10-16 : 04:25:06
|
quote:
Originally posted by visakh16
quote:
Originally posted by Sachin.Nand
quote:
Originally posted by visakh16
One practical scenario where you use cursors is when you want to execute a stored procedure passing values from rows of a table and you want to retrieve the results for each row of the table.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
I think with table valued parameters there is no need to use a cursor in the above scenario.PBUH
yeah thats truefrom sql 2008 onwards you've that featureTill that time we use to implement it using cursor.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Nope I used to used XML and shred the values of XML with the native support that TSQL used to give since SQL Server 7.0 before the advent of XQuery in SQL 2005.PBUH |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Sachin.Nand
2937 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-17 : 01:30:17
|
quote:
Originally posted by Sachin.Nand
quote:
Originally posted by visakh16
quote:
Originally posted by Sachin.Nand
quote:
Originally posted by visakh16
One practical scenario where you use cursors is when you want to execute a stored procedure passing values from rows of a table and you want to retrieve the results for each row of the table.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
I think with table valued parameters there is no need to use a cursor in the above scenario.PBUH
yeah thats truefrom sql 2008 onwards you've that featureTill that time we use to implement it using cursor.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Nope I used to used XML and shred the values of XML with the native support that TSQL used to give since SQL Server 7.0 before the advent of XQuery in SQL 2005.PBUH
Yep. thats another way especially with advanced functions provided from sql 2005 onwards for XML shredding------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-10-17 : 12:25:15
|
quote:
Originally posted by tkizer
quote:
Originally posted by subhaoviya
Hi friends, my knowledge is about cursor it that it is used for row by row iteration. we can avoid it by using while looping based on index column.
A while loop is really no better than a cursor. You need to instead think in sets and not row by row processing.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
I agree with Tara on trying to avoid non-set-based solutions. The biggest issue with cursors, besides the RBAR (Row-By-Agonizing-Row) aspect, is that they are not memory safe. Meaning, you can EASILY take down a server if you make a mistake with a cursor. Although, cursours can be faster than while loops, the potential for damage means I won't allow them. Assuming I have that level of control, of course. :) |
|
|
|
BruceT
Yak Posting Veteran
78 Posts |
Posted - 2011-10-17 : 15:58:41
|
| I have one situation where I could come up with no other way than to use a cursor. I posted the problem on this and SQLCentral (a while ago) and no one could come up with a non-cursor based solution. Might be different with new 2008 features, but probably not. I have to build dynamic sql statements based on user configured data stored in rows (on a row by row basis). Based on what is returned by the dynamic sql I either have to process and build a dynamic select for the next row or I'm done. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
BruceT
Yak Posting Veteran
78 Posts |
Posted - 2011-10-17 : 16:19:32
|
quote:
Originally posted by tkizer
Bruce, there are plenty of examples that require row by row processing. It sounds like you've got a design problem that is leading to your need to do this. Often times unless you can change your design, you are stuck with this.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Exactly. The process picks a fee rate based on data ranges stored in user definable tables and columns with no way to know before hand what the tables, columns, and data ranges are. Flexibility at the cost of performance...or as I like to put it...With great flexibility comes great complexity! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-10-17 : 16:44:15
|
| You might want to check this out Bruce: http://goo.gl/BMR6YIt's a more structured way of doing what you describe. Not sure if you can modify your DB to use it but might give you ideas. |
|
|
|
BruceT
Yak Posting Veteran
78 Posts |
Posted - 2011-10-17 : 17:00:15
|
quote:
Originally posted by robvolk
You might want to check this out Bruce: http://goo.gl/BMR6YIt's a more structured way of doing what you describe. Not sure if you can modify your DB to use it but might give you ideas.
Thanks Rob,I've looked into similar approaches. The problem is, I have no way of knowing at design time what tables and or columns are even used by this thing. They can literally be user created tables that it runs off of. So it uses a table that contains the table name, column name in the that table, min/max values to check for in that column, and fee rate to return. This is simplified, but that's the gist. |
|
|
|
|
when th cursors are prefered?