CODEWITHSUNDEEP
sqlsql-servercursornested
J.Lush
J.Lush

Reputation: 25

nested Cursor TSQL (SQL Server)

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

...

Upvotes: 0

Views: 1929

Answers (1)

dnoeth
dnoeth

Reputation: 60472

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

Upvotes: 1

Related Questions