Author |
Topic |
rama108
Posting Yak Master
115 Posts |
Posted - 2012-09-21 : 07:27:25
|
I have the following cursor within cursor, how can I use WHILE instead of cursor. What other alternatives do I have?DECLARE DCursor cursor FORSELECT ID, Name FROM #tblDorder by Name DESC OPEN DCursorFETCH NEXT FROM DCursor INTO @Id, @NameWHILE @@FETCH_STATUS = 0BEGIN DECLARE DECursor cursor FOR SELECT Id FROM #tblD WHERE Id = @Id OPEN DECursor FETCH NEXT FROM DECursor INTO @Id WHILE @@FETCH_STATUS = 0 |
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-09-21 : 07:32:37
|
Is there a problem that needs to be solved?The given code is senseless... Too old to Rock'n'Roll too young to die. |
|
|
rama108
Posting Yak Master
115 Posts |
Posted - 2012-09-21 : 07:37:59
|
Webfred, if you cannot read english, please do not reply. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-09-21 : 07:43:31
|
quote: Originally posted by rama108 Webfred, if you cannot read english, please do not reply.
Rama, if you can't post code that makes sense, don't post.Webfred is absolutely right, and we can't help with the piece of code you've posted. Your reply does nothing to explain it. |
|
|
rama108
Posting Yak Master
115 Posts |
Posted - 2012-09-21 : 08:16:15
|
I have the following cursor within cursor, how can I use WHILE instead of cursor. What other alternatives do I have? Please see my explanation and question in red below:DECLARE DCursor cursor FORSELECT ID, Name FROM #tblDorder by Name DESC OPEN DCursorFETCH NEXT FROM DCursor INTO @Id, @Name WHILE @@FETCH_STATUS = 0 BEGIN DECLARE DECursor cursor FOR SELECT Id FROM #tblD WHERE Id = @Id -- this @Id is the reason why I am using a cursor. Is there a way that I can use this @Id value with a While statement? OPEN DECursor FETCH NEXT FROM DECursor INTO @Id WHILE @@FETCH_STATUS = 0 Begin ....... END |
|
|
rama108
Posting Yak Master
115 Posts |
Posted - 2012-09-21 : 08:59:06
|
Here I cannot indent the code so it is not very readable. Does the above explanation makes sense? Please copy and paste the code in your query analyzer and indent it to make it readable, then you will see what I am asking.Thanks. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-09-21 : 09:53:19
|
You can indent code by enclosing it in [code] [/code] blocks. The forum software will format it as fixed-width and preserve spaces and tabs.DECLARE DCursor cursor FORSELECT ID, Name FROM #tblDorder by Name DESC OPEN DCursorFETCH NEXT FROM DCursor INTO @Id, @Name WHILE @@FETCH_STATUS = 0 BEGIN DECLARE DECursor cursor FOR SELECT Id FROM #tblD WHERE Id = @Id -- this @Id is the reason why I am using a cursor. Is there a way that I can use this @Id value with a While statement? OPEN DECursor FETCH NEXT FROM DECursor INTO @Id WHILE @@FETCH_STATUS = 0 Begin END The problem is that indenting doesn't explain what the code does, or what you're trying to accomplish. You're asking for an "alternative" to a cursor using a while loop, but that doesn't explain what the code is doing, and you didn't post the code for us to examine. It's impossible to answer a question without these details. If in doubt, post all the code, at least that's a starting point.The only thing I can derive from this snippet is that the DECursor is useless, as you are 1) only selecting the ID column from #tblD and 2) only those ID values that match the @Id variable from the other cursor. You're also fetching the exact same ID value back into the same @ID variable. This is almost equivalent to the following:WHILE @a=@a BEGINSET @a=@aEND Which I'm sure you'll agree doesn't accomplish a lot, so turning this into a WHILE loop is pointless. Since the code that actually does something wasn't included, I can't make any other suggestions.For future reference, if you're asking for help and someone asks for more explanation, you'll get a better and faster response by accommodating them. We're not mind readers and we answer these questions in our spare time, for free. If we're asking for more information, it's because we can't figure it out from what you've posted, and we have a lot of experience figuring things out with limited information.Also, you are far better off explaining WHAT you are trying to accomplish, not HOW you want to accomplish it. I can say from too much experience that nested cursors are never the right or best solution to anything, nor is changing them to WHILE loops. JOINs are your friend in SQL Server. |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2012-09-25 : 00:47:19
|
changing a cursor into a while loop will not make anything faster. both are RBAR. think in sets! elsasoft.org |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
|
|