nested Cursor TSQL (SQL Server)

Question

I'm Working with SQL Server and try to build a nested cursor. (I know cursors aren´t the best way to do things, but I can´t come up with any other solution).

So the first Cursor is going through a temporary Table ##Flanschbreite an catches FlanschMin into a variable. It works fine. But the inner cursor should do the same by catching FlanschMin into the variable @FL2. I want to create a temporary table which combines all possible combinations of these two variables. The outer cursor works fine, but the inner cursor isn´t going through the temporary table, it just returns the first entry. I think there is a problem with the @@FetchStatus.

Is there something obvious I´m doing wrong? (sorry for my bad English, I´m not a native speaker)

    DECLARE curFL1 CURSOR FOR
    SELECT FlanschMin FROM ##FlanschBreite;
    OPEN curFL1 

    FETCH next FROM curFL1 INTO @FL1                            
    WHILE @@Fetch_Status = 0

    BEGIN

        SELECT FlanschMin FROM ##FlanschBreite;
        OPEN curFL2

        FETCH next FROM curFL2 INTO @FL2                            
        WHILE @@Fetch_Status = 0

          BEGIN
            SET @IDFLansch += 1                                                 
            INSERT INTO ##FlanschZuweisung (IDFZ, FL1, FL2) VALUES (@IDFlansch, @FL1, @FL2)
            FETCH next FROM curFL2
          END
        CLOSE curFL2
        DEALLOCATE curFL2

    FETCH next FROM curFL1 INTO @FL1
    END
    CLOSE curFL1
    DEALLOCATE curFL1

My result for ##Flanschzuweisung:

IDFZ\ FL1\ FL2
1\     6\   6 
2\     8\   6    
3\    10\   6
4\     6\   6
5\     8\   6
...

What i need it to be:

IDFZ\ FL1\ FL2
1\     6\   6
2\     8\   6
3\    10\   6
4\     6\   8
5\     8\   8    
6\    10\   8
7\     6\  10

...

Probably CROSS JOIN would do the same. Better share input data and desired output. — Lukasz Szozda
– Lukasz Szozda, Commented Sep 24, 2015 at 14:45
There is a probably a way to do what you want with no cursors. Ask another question and describe what you want to accomplish. — Gordon Linoff
– Gordon Linoff, Commented Sep 24, 2015 at 14:51
Is there a reason you are using global temp tables instead of regular temp tables? Global temp tables have a number of challenges to deal with. — Sean Lange
– Sean Lange, Commented Sep 24, 2015 at 16:03

dnoeth · Accepted Answer · 2015-09-24 15:06:15Z

1

Where do you declare curFL2?

But anyway, this seems to be a simple CROSS JOIN plus a ROW_NUMBER:

-- INSERT INTO ##FlanschZuweisung (IDFZ, FL1, FL2)
SELECT row_number() over (order by t1.FlanschMin, t2. FlanschMin),
   t1.FlanschMin, t2. FlanschMin
FROM ##FlanschBreite as t1 CROSS JOIN ##FlanschBreite as t2

answered Sep 24, 2015 at 15:06

dnoeth

60.5k4 gold badges45 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

nested Cursor TSQL (SQL Server)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related