Cursor alternative - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Cursor alternative

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 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
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 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
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 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
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

Posted - 2012-09-25 : 11:10:21

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources