T-Sql cursor not proceeding on fetch

Question

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!

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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.