Solved

Cursor in Stored Procedure stuck in loop

Posted on 2013-11-18
2
343 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

821 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