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

• p_j_dave

  SSC Journeyman

  Points: 90

  More actions

  April 9, 2009 at 4:16 am

  #201900

  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

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  April 9, 2009 at 4:24 am

  #974916

  The BEGIN is in the wrong place. Here's the example from BOL:

  DECLARE Employee_Cursor CURSOR FOR

  SELECT EmployeeID, Title FROM AdventureWorks.HumanResources.Employee;

  OPEN Employee_Cursor;

  FETCH NEXT FROM Employee_Cursor;

  WHILE @@FETCH_STATUS = 0

  BEGIN

  FETCH NEXT FROM Employee_Cursor;

  END;

  CLOSE Employee_Cursor;

  DEALLOCATE Employee_Cursor;

  Just as a matter of interest, what are you planning to do with @dirid and @topic?

  Cheers

  ChrisM

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden