Is Nested Cursors Possible ???

Question

Is Nested Cursors Possible ???

vaibhav.a

Right there with Babe

Points: 719
More actions
January 7, 2009 at 5:18 am

#129735

is it possible to use nested cursors......I want to use cursors inside cursors in my SP.....

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

mmm

I wish I could lie to you , but yes it is possible, but as most people here will tell you, you should avoid using cursor and definitely avoid nested cursor,

could you post your logic here and we can try and help with a possible set-based solution.

thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

vaibhav.a (1/7/2009)
is it possible to use nested cursors......I want to use cursors inside cursors in my SP.....

Yes you can, but be aware that cursors will almost always be resource hogs. Very few situations require row-by-row processing. Unless yours is one of the few, then you would benefit from seeking a set-based solution.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

As Christopher Stobbs suggested, post your dilemna and we can try to find a more efficient solution than nested cursors. This is one of those problems that most of the gurus here like to take on.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

But before you post your problem, you will notice that several of us have a link to an article related to "How to ask for help" (we also use different verbage for the same thing).

Take the time to read this article, and follow the guidelines it provides for posting problems like yours. You will get better and TESTED answers in response to your post.

I also agree with the others, cursors may be evil, but nested cursors are even worse. If there is a set-based solution to your problem, there are many gurus on this site that will work to help you find it. Just be patient with us as we do this for free and still have our real jobs to do as well to pay the bills.

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 5

And, if you really think you have to use nested cursors - review the following article that describes a couple of ways of optimizing cursor operations:

http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx

As others have already said - you probably don't even need a cursor to solve your problem. If you post the problem, somebody will definitely be able to help you solve it using a set-based solution that will most likely perform much better than a nested cursor solution.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

vaibhav.a Right there with Babe Points: 719 More actions · Answer 6

the sitution is like that i am having around 200 records in my temperory table taken in sp named "BillView".

and i want to update two or more columns based on corresponding columns and other conditions for each row...... so i have the requirement to use the cursors so as to avoid traversing on another unwanted rows .......

and one thing more i am executing queries in SP in string format like

exec('select'+ @MLoc+'=MLocation.Name from Mloc');

but my SP is going sometimes infinite or sometimes takes too much time to give output.........i dont know what i have to do next ...... is it because cursors are slow and i m using nested cursors........

plz help me out

thanks

vaibhav

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

Please post the DDL, some test data, requirements, and the desired results. I can almost guarantee you that we can eliminate the existing cursors.

Yes the cursors are definitely part of the performance problem and most likely 90% of it.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Again, you really need to read this article: http://www.sqlservercentral.com/articles/Best+Practices/61537/

You only THINK you need to use a cursor at this moment. If you provide the information in the format recommended by the article we are all tell you to read, you will get a much better response to your request for help. Not only that, the code you will get in return will also be tested because you will have provided us with everything we need to help you solve your problem.

Please read the article, following its guidelines for posting everything you need to get quality help. There are many of us willing to help if you do this one small thing we ask.

Based solely on what you have posted so far, we really can't help you that well. Also, just so that you understand, using a cursor over 100 or 200 rows may not be that bad, but imagine what your code would be like if that jumped to 100,000 or even 1,000,000 rows; how performant would that cursor based code be then. Think of it this way, using a cursor to process 1 row a second, how long will it take to do 1,000,000 rows? (Hint; try over 11.5 DAYS!)

There is a set-based solution. We need to know what your requirements are, what the tables look like, some sample data, and the expected results to really help you out.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

Read the links, post your table definitions, tell us what you want to do with the data. I doubt that you need cursors and I am certain that you will be much happier without them.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]