| Author |
 Topic  |
|
|
rama108
Yak Posting Veteran
72 Posts |
 Posted - 09/21/2012 : 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 FOR
SELECT ID, Name FROM #tblD
order by Name DESC
OPEN DCursor
FETCH NEXT FROM DCursor INTO @Id, @Name
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE DECursor cursor FOR
SELECT Id FROM #tblD
WHERE Id = @Id
OPEN DECursor
FETCH NEXT FROM DECursor INTO @Id
WHILE @@FETCH_STATUS = 0 |
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 09/21/2012 : 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
Yak Posting Veteran
72 Posts |
Posted - 09/21/2012 : 07:37:59
|
| Webfred, if you cannot read english, please do not reply. |
|
|
|
robvolk
Most Valuable Yak
USA
15557 Posts |
Posted - 09/21/2012 : 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
Yak Posting Veteran
72 Posts |
Posted - 09/21/2012 : 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 FOR
SELECT ID, Name FROM #tblD
order by Name DESC
OPEN DCursor
FETCH 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
Yak Posting Veteran
72 Posts |
Posted - 09/21/2012 : 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
USA
15557 Posts |
Posted - 09/21/2012 : 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 FOR
SELECT ID, Name FROM #tblD
order by Name DESC
OPEN DCursor
FETCH 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 BEGIN
SET @a=@a
END 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
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 09/25/2012 : 00:47:19
|
changing a cursor into a while loop will not make anything faster. both are RBAR.
think in sets!
elsasoft.org |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3821 Posts |
|
| |
Topic |
|
Cursor alternative
