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

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

CodePudding user response：

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