Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

Cursor in Stored Procedure stuck in loop

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
soozh
Asked:
soozh
1 Solution
 
Robert SchuttSoftware EngineerCommented:
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
 
soozhAuthor Commented:
I must be blind or stupid not to have seen that!  Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now