Avoid Cursor [while loop example]

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

Transact-SQL (2000)

Avoid Cursor [while loop example]

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2006-06-26 : 03:46:10

I need to call a stored procedure when a column in a particular row meets certain condition. You don't have to use cursors for this. This can be achieved using WHILE loop, as long as there is a unique key to identify each row. For examples of using WHILE loop for row by row processing

Could any body give me the example please

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-26 : 04:16:39

quote:
Originally posted by kamii47

I need to call a stored procedure when a column in a particular row meets certain condition. You don't have to use cursors for this. This can be achieved using WHILE loop, as long as there is a unique key to identify each row. For examples of using WHILE loop for row by row processing

Could any body give me the example please

Where did you read that?
Refer this to know how to use While Loop in place of Cursor
http://sqlteam.com/forums/topic.asp?TOPIC_ID=65341

Madhivanan

Failing to plan is Planning to fail

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-06-26 : 13:46:39

While loop is not better then cursor. Problem with cursor is row-by-row processing and while loop doesn't cure that.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-06-26 : 17:25:37

My guess is that the SP the poster wants to call works on one <id> per call. They probably don't want to (or can't) re-write the SP logic to work on the entire set of data. That would be the non-cursor/non-looping, set-based way to handle it. That would also be the more efficient way to do it. However, if that is impossible for some reason, here is an alternative to madhivanan's link:


declare @i int
select @i = min(id) from sysobjects
while @i is not null
begin
	print @i --exec MySP @i = @i
	select @i = min(id) from sysobjects where id > @i
end

Be One with the Optimizer
TG

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-26 : 18:08:13

Looks to me like the orginal post was a cut and paste from homework, interview question, or certification book.

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-27 : 01:55:09

quote:
Originally posted by Michael Valentine Jones

Looks to me like the orginal post was a cut and paste from homework, interview question, or certification book.

CODO ERGO SUM

Thats why I asked the questioner "Where did you read that?"

Madhivanan

Failing to plan is Planning to fail

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2009-06-29 : 00:55:49

I myself have answered lots of very beginner question in Asp.net forum but never comment like above.
You might be good in one thing and not in other.
Anyway thanks for other guys mmarovic and TG

Kamran Shahid
Sr. Software Engineer
Assurety Consulting inc.
(MCSD.Net,MCPD.net)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-06-29 : 09:37:16

You're posting a reply after 3 years?

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-29 : 10:18:26

quote:
Originally posted by kamii47

I myself have answered lots of very beginner question in Asp.net forum but never comment like above.
You might be good in one thing and not in other.
Anyway thanks for other guys mmarovic and TG

Kamran Shahid
Sr. Software Engineer
Assurety Consulting inc.
(MCSD.Net,MCPD.net)

Ok. Did you learn how to use while loop?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources