T-Sql cursor not proceeding on fetch

Hi,I know that cursors are frowned upon and I try to avoid their use as much as possible, but there may be some legitimate reasons to use them. I have one and I am trying to use a pair of cursors: one for the primary table and one for the secondary table. The primary table cursor iterates through the primary table in an outer loop. the secondary table cursor iterates through the secondary table in the inner loop. The problem is, that the primary table cursor though apparently proceeding and saving the primary key column value [Fname] into a local variable @Fname, but it does not get the row for the corresponding foreign key column in the secondary table. For the secondary table it always returns the rows whose foreign key column value matches the primary key column value of the first row of the primary table.

Following is a very simplified example for what I want to do in the real stored procedure. Names is the primary table

SET NOCOUNT ON
DECLARE 
    @Fname varchar(50) -- to hold the fname column value from outer cursor loop
    ,@FK_Fname varchar(50) -- to hold the fname column value from inner cursor loop
    ,@score int
;

--prepare primary table to be iterated in the  outer loop
DECLARE @Names AS Table (Fname varchar(50))
INSERT @Names
    SELECT 'Jim' UNION
    SELECT 'Bob' UNION
    SELECT 'Sam' UNION
    SELECT 'Jo' 


--prepare secondary/detail table to be iterated in the inner loop
DECLARE @Scores AS Table (Fname varchar(50), Score int)
INSERT @Scores
    SELECT 'Jo',1 UNION
    SELECT 'Jo',5 UNION
    SELECT 'Jim',4 UNION
    SELECT 'Bob',10 UNION
    SELECT 'Bob',15 

--cursor to iterate on the primary table in the outer loop
DECLARE curNames CURSOR
FOR SELECT Fname FROM @Names


OPEN curNames
FETCH NEXT FROM curNames INTO @Fname

--cursor to iterate on the secondary table in the inner loop
DECLARE curScores CURSOR
FOR 
    SELECT FName,Score 
    FROM @Scores 
    WHERE Fname = @Fname 
 --*** NOTE: Using the primary table's column value @Fname from the outer loop

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Outer loop @Fname = ' + @Fname

    OPEN curScores
    FETCH NEXT FROM curScores INTO @FK_Fname, @Score

    WHILE @@FETCH_STATUS = 0
    BEGIN
     PRINT ' FK_Fname=' + @FK_Fname + '. Score=' + STR(@Score)
     FETCH NEXT FROM curScores INTO @FK_Fname, @Score
    END
    CLOSE curScores
    FETCH NEXT FROM curNames INTO @Fname
END

DEALLOCATE curScores

CLOSE curNames
DEALLOCATE curNames

Here is what I get for the result. Please note that for the outer loop it DOES show the up-to-date Fname, but when that Fname is used as @Fname to fetch the relevant row from the secondary table for the succeeding iterations, it still get the rows that match the first row (Bob) of the primary table.

Outer loop @Fname = Bob
    FK_Fname=Bob. Score=10
    FK_Fname=Bob. Score=15
Outer loop @Fname = Jim
    FK_Fname=Bob. Score=10
    FK_Fname=Bob. Score=15
Outer loop @Fname = Jo
    FK_Fname=Bob. Score=10
    FK_Fname=Bob. Score=15
Outer loop @Fname = Sam
    FK_Fname=Bob. Score=10
    FK_Fname=Bob. Score=15

Please let me know what am I do wrong. Thanks in advance!

From stackoverflow Aamir Ghanchi

• I'd try placing the

  DECLARE curScores CURSOR
  FOR 
      SELECT FName,Score 
      FROM @Scores 
      WHERE Fname = @Fname
  

  inside the first while, beacuse you're declaring the cursor only for the first name value

  From Eduardo Campañó

• The value of @fName is evaluated at :DECLARE curScores CURSOR and not in the primary loop. You must Declare and then deallocate the secon cursor in the primary loop.

  Aamir Ghanchi : Thanks Ovidiu. that did the trick!
  From Ovidiu Pacurar

• Thanks to few hints, I was able to find the solution.

  I had to DECLARE and DEALLOCATE the secondary cursor within the first loop. I initially hated to do it as I thought alocating and deallocating resources in the loop was not a good idea, but I think there is no other way to avoid this in this particular situation. Noew the working code looks some thing like this:

  SET NOCOUNT ON
  DECLARE 
      @Fname varchar(50) -- to hold the fname column value from outer cursor loop
      ,@FK_Fname varchar(50) -- to hold the fname column value from inner cursor loop
      ,@score int
  ;
  
  --prepare primary table to be iterated in the  outer loop
  DECLARE @Names AS Table (Fname varchar(50))
  INSERT @Names
      SELECT 'Jim' UNION
      SELECT 'Bob' UNION
      SELECT 'Sam' UNION
      SELECT 'Jo' 
  
  
  --prepare secondary/detail table to be iterated in the inner loop
  DECLARE @Scores AS Table (Fname varchar(50), Score int)
  INSERT @Scores
      SELECT 'Jo',1 UNION
      SELECT 'Jo',5 UNION
      SELECT 'Jim',4 UNION
      SELECT 'Bob',10 UNION
      SELECT 'Bob',15 
  
  --cursor to iterate on the primary table in the outer loop
  DECLARE curNames CURSOR
  FOR SELECT Fname FROM @Names
  
  
  OPEN curNames
  FETCH NEXT FROM curNames INTO @Fname
  
  --cursor to iterate on the secondary table in the inner loop
  DECLARE curScores CURSOR
  FOR 
      SELECT FName,Score 
      FROM @Scores 
      WHERE Fname = @Fname 
   --*** NOTE: Using the primary table's column value @Fname from the outer loop
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
      PRINT 'Outer loop @Fname = ' + @Fname
  
      OPEN curScores
      FETCH NEXT FROM curScores INTO @FK_Fname, @Score
  
      WHILE @@FETCH_STATUS = 0
      BEGIN
          PRINT ' FK_Fname=' + @FK_Fname + '. Score=' + STR(@Score)
          FETCH NEXT FROM curScores INTO @FK_Fname, @Score
      END
      CLOSE curScores
      FETCH NEXT FROM curNames INTO @Fname
  END
  
  DEALLOCATE curScores
  
  CLOSE curNames
  DEALLOCATE curNames
  

  And I am getting the right results:

  Outer loop @Fname = Bob
      FK_Fname=Bob. Score=        10
      FK_Fname=Bob. Score=        15
  Outer loop @Fname = Jim
      FK_Fname=Jim. Score=         4
  Outer loop @Fname = Jo
      FK_Fname=Jo. Score=         1
      FK_Fname=Jo. Score=         5
  Outer loop @Fname = Sam
  

  From Aamir Ghanchi

• I think you could do this so much easier with temp tables that have row numbers:

  create table #temp1
  (
   row int identity(1,1)
   , ... 
  )
  

  It really looks like you're asking SQL to behave like a language that likes loops. It doesn't. Whenever I find myself writing a loop in SQL I ask myself, does it have to be done this way? 7/10 times the answer is no, I can do it with sets instead.

  From jcollum