Nested WHILE loop in SQL runs the inner loop, but does not run the outer loop

Question

I create a procedure to consolidate all info calculated per each X & Y value combinations. I use a simple indexing to fetch the values of X & Y by using 2-step WHILE loops.

Here are the X & Y support tables:

However, the result is below as [MAIN_TABLE]. However, I expect it to be the all combinations of X & Y.

Here is my code:

ALTER PROCEDURE [dbo].[CONSOLIDATION_Procedure]     
AS
BEGIN
    
    IF OBJECT_ID('dbo.[MAIN_TABLE]', 'U') IS NOT NULL
         DELETE FROM [MAIN_TABLE]
    
    DECLARE @IndexX INT SET @IndexX=1
    DECLARE @IndexY INT SET @IndexY=1
    DECLARE @IndexX_Max INT SET @IndexX_Max = (SELECT MAX([Index]) FROM [temp_X_Index])
    DECLARE @IndexY_Max INT SET @IndexY_Max = (SELECT MAX([Index]) FROM [temp_Y_Index])
    
    WHILE @IndexX <= @IndexX_Max 
    BEGIN         
        DECLARE @XVal VARCHAR(20) SET @XVal = (SELECT [X_VAL] FROM [temp_X_Index] WHERE [INDEX]=@IndexX)
    
        WHILE @IndexY <= @IndexY_Max 
        BEGIN         
            DECLARE @YVAL VARCHAR(20) SET @YVAL = (SELECT [Y_VAL] FROM [temp_Y_Index] WHERE [INDEX]=@IndexY)            
            INSERT INTO [MAIN_TABLE]        
                SELECT * FROM [dbo].[SUPPORT_TABLE] WHERE [X] = @XVal AND [Y]= @YVAL
        
        SET @IndexY = @IndexY+1
        END
    
    SET @IndexX = @IndexX+1
    END
    
  END

Farshid Shekari · Accepted Answer · 2022-08-26 08:42:30Z

4

You can use a Cartesian product(cross join) instead of this block code to generate it.

SELECT x.[index] x_index, y.[index] as y_index, x_val, y_val
  FROM [temp_X_Index] x
 CROSS JOIN [temp_Y_Index] y

edited Aug 26, 2022 at 8:42

answered Aug 26, 2022 at 8:28

Farshid Shekari

2,4394 gold badges31 silver badges48 bronze badges

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

7 Comments

tinazmu Over a year ago

Can also use cross join to mean the same thing (bit more explicit)

Nick.Mc Over a year ago

Specifically I think @tinazmu means use this syntax FROM X CROSS JOIN Y it makes the meaning a little more obvious

Farshid Shekari Over a year ago

@Nick.McDermaid and tinazmu I update to with that keyword

Farshid Shekari Over a year ago

@BTurkeli I mean this code will do all you need to do.

Thom A Over a year ago

@BTurkeli it is very rare you ever need a WHILE loop in SQL. SQL is a set-based language, and so it excels at set-based operations; a WHILE is the opposite to that methodology. If you are considering using a WHILE when writing SQL, you are very likely making the wrong decisions.

|

Collectives™ on Stack Overflow

Nested WHILE loop in SQL runs the inner loop, but does not run the outer loop

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related