Solved

Cursor in Stored Procedure stuck in loop

Posted on 2013-11-18
2
337 Views
Last Modified: 2013-11-18
Hello,

I have a stored procedure that creates a temporary table (with a call to GetGroupNames).

I then want to create and open a cursor to call [CheckMandatoryField] for each row in the table.

It appears to me that the code below never loops through the table, and appears to set @GroupFieldName to the first row in the table each time.

I tested by adding the select statement: select @GroupFieldName.

Can anyone see what i am doing wrong?


 -- Now validate all the other groups.
  select * into #Groups  
  from dbo.GetGroupnames( @doc_id )
  order by ag_Name

  declare @GroupFieldName nvarchar(100);
  
  declare Group_Cursor cursor for
  select ag_Name from #Groups ;
  
  open Group_Cursor ;
  
  fetch next from Group_Cursor into @GroupFieldName
  while (@@fetch_status = 0) 
  begin 
   select @GroupFieldName ;
   
    -- Validate the group
 --   exec @return_value = [CheckMandatoryField] @doc_id = @doc_id, @ddt_FieldName = @GroupFieldName
   -- if (@return_value = 0) set @Valid = 0 ;
  end ;
  
  close Group_Cursor ;
  deallocate Group_Cursor ;
  drop table #Groups ;

Open in new window

0
Comment
Question by:soozh
2 Comments
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 500 total points
ID: 39657567
you need to repeat your "fetch next" statement before line 21, as last statement inside the while loop. See also http://technet.microsoft.com/en-us/library/ms180152%28v=sql.100%29.aspx (under Examples)
 -- Now validate all the other groups.
  select * into #Groups  
  from dbo.GetGroupnames( @doc_id )
  order by ag_Name

  declare @GroupFieldName nvarchar(100);
  
  declare Group_Cursor cursor for
  select ag_Name from #Groups ;
  
  open Group_Cursor ;
  
  fetch next from Group_Cursor into @GroupFieldName
  while (@@fetch_status = 0) 
  begin 
   select @GroupFieldName ;
   
    -- Validate the group
 --   exec @return_value = [CheckMandatoryField] @doc_id = @doc_id, @ddt_FieldName = @GroupFieldName
   -- if (@return_value = 0) set @Valid = 0 ;

    -- added next line (copy of same line before while loop)
    fetch next from Group_Cursor into @GroupFieldName

  end ;
  
  close Group_Cursor ;
  deallocate Group_Cursor ;
  drop table #Groups ;

Open in new window

0
 

Author Closing Comment

by:soozh
ID: 39657676
I must be blind or stupid not to have seen that!  Thanks
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
how to fix this error 14 57
combine an MS SQL string in Idera DM 9 49
Insert with SET how to handle join 6 57
Oracle - Create Procedure with Paramater 16 57
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now