SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Cursor alternative
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rama108
Posting Yak Master

109 Posts

Posted - 09/21/2012 :  07:27:25  Show Profile  Reply with Quote
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
8766 Posts

Posted - 09/21/2012 :  07:32:37  Show Profile  Visit webfred's Homepage  Reply with Quote
Is there a problem that needs to be solved?
The given code is senseless...


Too old to Rock'n'Roll too young to die.
Go to Top of Page

rama108
Posting Yak Master

109 Posts

Posted - 09/21/2012 :  07:37:59  Show Profile  Reply with Quote
Webfred, if you cannot read english, please do not reply.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15683 Posts

Posted - 09/21/2012 :  07:43:31  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

rama108
Posting Yak Master

109 Posts

Posted - 09/21/2012 :  08:16:15  Show Profile  Reply with Quote
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
Go to Top of Page

rama108
Posting Yak Master

109 Posts

Posted - 09/21/2012 :  08:59:06  Show Profile  Reply with Quote
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.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15683 Posts

Posted - 09/21/2012 :  09:53:19  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 09/25/2012 :  00:47:19  Show Profile  Visit jezemine's Homepage  Reply with Quote
changing a cursor into a while loop will not make anything faster. both are RBAR.

think in sets!


elsasoft.org
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 09/25/2012 :  11:10:21  Show Profile  Reply with Quote
TO add to what Rob said:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.34 seconds. Powered By: Snitz Forums 2000