Iterate over two loops (CURSOR and WHILE) and print the records grouped by location

I want to print the records grouped by each location (in this case they are 2), duly ordered.

I’m testing with a cursor but it prints the same location twice.

How can I fix this?

    DECLARE @DateMovementsTopWorst AS DATE;
    DECLARE @InventoryOnSiteTopWorst AS NUMERIC;
    DECLARE @FamilyTopWorst AS VARCHAR(50)
    DECLARE @BufferTopWorst AS NUMERIC;
    DECLARE @SkuTopWorst AS VARCHAR(50);
    DECLARE @LocationTopWorst AS VARCHAR(50);
    DECLARE @Counter3 AS INT = 1;
    DECLARE @Location AS VARCHAR(50);
    DECLARE @Temporal2 AS INT = 10;
    DECLARE ExternCursor CURSOR
        FOR SELECT DISTINCT(LOCATION) FROM TFSCM_CLI_BUFFER

    OPEN ExternCursor
    FETCH NEXT FROM ExternCursor INTO @Location;
    WHILE @@FETCH_STATUS = 0 
    --WHILE @Counter3 <  @Temporal+1
    BEGIN
        DECLARE @Counter2 AS INT = 1;
        --SET ROWCOUNT @Counter3;
        PRINT ' SKU: '
            + '     Size: '
            + '     Family: '
            + '     Inventory: '
            + '     Movement Date: '

        WHILE @Counter2 < 6
        BEGIN
            SET ROWCOUNT @Counter2;

                SELECT @SkuTopWorst= SKU,
                    @BufferTopWorst= BUFFER,
                    @FamilyTopWorst = FAMILY,
                    @InventoryOnSiteTopWorst = TOTAL_INVENTORY_ON_SITE,
                    @DateMovementsTopWorst = UPDATE_MOVEMENTS,
                    @LocationTopWorst = LOCATION,
                    @Temporal2 = COUNT(*) OVER ()
                FROM TFSCM_CLI_BUFFER
                WHERE LOCATION = @Location
                ORDER BY TOTAL_INVENTORY_ON_SITE ASC;
                PRINT @SkuTopWorst
                    + ' '
                    + CONVERT(VARCHAR(100), @BufferTopWorst)
                    + '     '
                    +  @FamilyTopWorst
                    + '         '
                    + CONVERT(VARCHAR(100), @InventoryOnSiteTopWorst)
                    + '         '
                    +  CONVERT(VARCHAR(100), @DateMovementsTopWorst);
                SET @Counter2 = @Counter2+1;
        END
        PRINT 'This is: ' + @Location;
        --SET @Counter3 = @Counter3+1;
        FETCH NEXT FROM ExternCursor;
    END
    CLOSE ExternCursor
    DEALLOCATE ExternCursor

Edited with suggestions from users. But we have the same results

Answer

You’re not using the cursor data for anything – therefore you inner query gets all your locations. You need to obtain the value from the cursor and then use it to filter your inner query. The following changes are required:

Add DECLARE @Location AS VARCHAR(50); near the start.
Modify your fetch‘s to be FETCH NEXT FROM ExternCursor INTO @Location;
Modify your inner where to be WHERE [LOCATION] = @Location

Advertisement

Answer