simple cursor runs infinite loop

• Marcus Farrugia

  SSCarpal Tunnel

  Points: 4718

  More actions

  December 23, 2014 at 12:13 pm

  #397806

  Hi, I'm trying to build a simple cursor to understand how they work. From the temp table, I would like to print out the values of the table, when I run my cursor it just keeps running the output of the first row infinitely. I just want it to print out the 7 rows in the table ...

  IF OBJECT_ID('TempDB..#tTable','U') IS NOT NULL

  DROP TABLE #tTable

  CREATE TABLE #tTable

  (

  tID int,

  minValue int,

  maxValue int,

  tName varchar(25)

  )

  insert into #tTable

  (tID, MinValue, MaxValue, tName)

  SELECT '1','0','3','0-3 Mths' UNION ALL

  SELECT '2','3','6','3-6 Mths' UNION ALL

  SELECT '3','6','9','6-9 Mths' UNION ALL

  SELECT '4','9','12','9-12 Mths' UNION ALL

  SELECT '5','12','18','12-18 Mths' UNION ALL

  SELECT '6','18','24','18-24 Mths' UNION ALL

  SELECT '7','24','9999','24+ Mths'

  select * from #tTable

  declare @tid as int;

  declare @min as int;

  declare @max as int;

  declare @tn as varchar(25);

  declare @otCursor as cursor;

  set @otCursor = cursor for

  select TenureID, MinMonths, MaxMonths, TenureName from #tTable;

  open @otCursor;

  fetch next from @otCursor into @tid,@min,@max,@tn

  while @@fetch_status = 0

  begin

  print

  cast(@tid as varchar(50)) + ' ' +

  cast(@min as varchar(50)) + ' ' + cast(@max as varchar(50)) + ' ' +

  @tn;

  end

  close @otCursor

  deallocate @otCursor

• Andrew Kernodle

  SSCertifiable

  Points: 5314

  More actions

  December 23, 2014 at 12:37 pm

  #1766254

  I believe what you need is a FETCH NEXT just before the END in your WHILE loop:

  while @@fetch_status = 0

  begin

  print

  cast(@tid as varchar(50)) + ' ' +

  cast(@min as varchar(50)) + ' ' + cast(@max as varchar(50)) + ' ' +

  @tn;

  FETCH NEXT FROM @otCursor into @tid,@min,@max,@tn

  end

  Without the FETCH NEXT in the loop, the cursor won't retrieve any more data from the target dataset, and you'll just keep repeating the loop, since @@FETCH_STATUS will never hit the end-of-dataset status level.

  -

• Marcus Farrugia

  SSCarpal Tunnel

  Points: 4718

  More actions

  December 23, 2014 at 12:38 pm

  #1766255

  Thank you!!!