cursor goes in to infinit loop.........

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

Transact-SQL (2005)

cursor goes in to infinit loop.........

Author

Topic

p_j_dave
Starting Member

1 Post

Posted - 2009-04-09 : 06:35:36

hi,
I have written a cursor that goes into infinite loop. I am not able to make out where i have done wrong.

the following is the code.
/******************************************************************************/
declare @dirid int
declare @topic varchar(8000)

create table #topic(Dir_Id int,topic_Id varchar(1000))

insert into #topic(Dir_Id,topic_Id)select D.Dir_Id,D.topicid FROM Category CN
INNER JOIN directory D ON CN.Cat_Id = D.OrgType_Id
INNER JOIN SubCategory SB ON D.Country = SB.SubCat_ID
WHERE CN.Cat_Name = 'Academic'

declare curtopic cursor for
select Dir_Id,topic_Id from #topic
open curtopic
fetch next from curtopic into @dirid,@topic
begin
while @@fetch_status = 0
set @topic = ''
SELECT @topic = topic_Id from #topic
fetch next from curtopic into @dirid,@topic
end
close curtopic
deallocate curtopic
print @topic
drop table #topic

/****************************************************************************/

i want to retrieve the data row by row for which i have written cursor.
but it is going in to infinite loop.

any help would be great full.

regards,
pranav

Pranav Dave

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-04-09 : 07:00:12

Why do you need a cursor for this?

What are you trying to do with @topic? Cursors should only be used for very specific processes and not as a general rule. If you can do this in a set based way, I would say change it to that, if not, you may want to think about other ways to do it.

Kokkula
Starting Member

41 Posts

Posted - 2009-04-13 : 06:07:11

Hello,

Try this

declare @dirid int
declare @topic varchar(8000)

create table #topic(Dir_Id int,topic_Id varchar(1000))

insert into #topic(Dir_Id,topic_Id)

select D.Dir_Id,D.topicid FROM Category CN
INNER JOIN directory D ON CN.Cat_Id = D.OrgType_Id
INNER JOIN SubCategory SB ON D.Country = SB.SubCat_ID
WHERE CN.Cat_Name = 'Academic'

declare curtopic cursor for
select Dir_Id,topic_Id from #topic
open curtopic
fetch next from curtopic into @dirid,@topic

while @@fetch_status = 0
BEGIN

set @topic = ''
SELECT @topic = topic_Id from #topic
print @topic

fetch next from curtopic into @dirid,@topic
end

close curtopic
deallocate curtopic

--print @topic
drop table #topic

There Should be a begin after while statement but it is before it. This caused teh Cursor to be infinite loop.

Hope helpful...

Thanks,
Pavan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources