How to execute multiple while loops or nested while loop from parameters in SQL Server

Question

I need to execute a script with multiple while loops. Example I need to insert data with an incremental and will also need to insert data based on date field.

While loops 1,3 are working fine. I'm able to insert data but, the while loop 2 is executed as a condition, which means only the starting value is inserted, and no other value is inserted.

Is there anything I'm missing or is it not possible?

I'm running this on Azure 12.0.2000.8

DECLARE @cnt INT = 1
DECLARE @Pcnt INT = 1

DECLARE @name VARCHAR(150), @startval decimal(16,8), @endval decimal(16,8), @incval decimal(16,8), @Startdt DATETIME, @Enddt DATETIME


WHILE @cnt<=(SELECT MAX(id)FROM #temp)   --- While loop 1
BEGIN

SELECT @name=name,@startval=startval,@endval=ENDval,@incval=inc,@Startdt=Startdt,@Enddt=ENDdt  FROM #temp where id=@cnt

        WHILE @Startdt<=@Enddt    -- While loop 2
        BEGIN 

                WHILE @startval<=@endval    --- While loop 3
                BEGIN

                        INSERT INTO #FinalTable
                        VALUES
                            (  @Startdt, @name, @startval, GETDATE() )

                        SET @startval=@startval+@incval
                END

        SET @Startdt= DATEADD(dy,7,@Startdt)

        END 

SET @cnt=@cnt+1

END

Below is my #temp Table result set:

id  name    startval    ENDval      inc         Startdt     ENDdt
1   Test1   0.00000000  0.95000000  0.19000000  2018-07-06  2019-03-22 
2   Test2   0.00000000  3.00000000  0.60000000  2018-07-06  2019-03-22 
3   Test3   0.00000000  2.50000000  0.50000000  2018-07-06  2019-03-22 
4   Test4   0.00000000  4.00000000  0.80000000  2018-07-06  2019-03-22

This is the result I'm getting for the current procedure,

SELECT WeekEndingDate,MetricType,MetricValue,InsertDate FROM #FinalTable

WeekEndingDate  MetricType  MetricValue InsertDate
2018-07-06 00:00:00.000 Test1   0.00000000  2019-05-07 18:14:29.080
2018-07-06 00:00:00.000 Test1   0.19000000  2019-05-07 18:14:29.080
2018-07-06 00:00:00.000 Test1   0.38000000  2019-05-07 18:14:29.080
2018-07-06 00:00:00.000 Test1   0.57000000  2019-05-07 18:14:29.080
2018-07-06 00:00:00.000 Test1   0.76000000  2019-05-07 18:14:29.080
2018-07-06 00:00:00.000 Test1   0.95000000  2019-05-07 18:14:29.080
2018-07-06 00:00:00.000 Test2   0.00000000  2019-05-07 18:14:29.080
2018-07-06 00:00:00.000 Test2   0.60000000  2019-05-07 18:14:29.080
2018-07-06 00:00:00.000 Test2   1.20000000  2019-05-07 18:14:29.080
2018-07-06 00:00:00.000 Test2   1.80000000  2019-05-07 18:14:29.080
2018-07-06 00:00:00.000 Test2   2.40000000  2019-05-07 18:14:29.080
2018-07-06 00:00:00.000 Test2   3.00000000  2019-05-07 18:14:29.080
2018-07-06 00:00:00.000 Test3   0.00000000  2019-05-07 18:14:29.080
2018-07-06 00:00:00.000 Test3   0.50000000  2019-05-07 18:14:29.080
2018-07-06 00:00:00.000 Test3   1.00000000  2019-05-07 18:14:29.080
2018-07-06 00:00:00.000 Test3   1.50000000  2019-05-07 18:14:29.080
2018-07-06 00:00:00.000 Test3   2.00000000  2019-05-07 18:14:29.080
2018-07-06 00:00:00.000 Test3   2.50000000  2019-05-07 18:14:29.080
2018-07-06 00:00:00.000 Test4   0.00000000  2019-05-07 18:14:29.080
2018-07-06 00:00:00.000 Test4   0.80000000  2019-05-07 18:14:29.080
2018-07-06 00:00:00.000 Test4   1.60000000  2019-05-07 18:14:29.080
2018-07-06 00:00:00.000 Test4   2.40000000  2019-05-07 18:14:29.080
2018-07-06 00:00:00.000 Test4   3.20000000  2019-05-07 18:14:29.080
2018-07-06 00:00:00.000 Test4   4.00000000  2019-05-07 18:14:29.083

I will actually need the result for #FinalTable as below, which is for Test1 we should have all the incremental values for each dates, but currently it only inserts records for : 2018-07-06 00:00:00.000 date.

WeekEndingDate  MetricType  MetricValue InsertDate
2018-07-06 00:00:00.000 Test1   0.00000000  2019-05-07 18:14:29.080
2018-07-06 00:00:00.000 Test1   0.19000000  2019-05-07 18:14:29.080
2018-07-06 00:00:00.000 Test1   0.38000000  2019-05-07 18:14:29.080
2018-07-06 00:00:00.000 Test1   0.57000000  2019-05-07 18:14:29.080
2018-07-06 00:00:00.000 Test1   0.76000000  2019-05-07 18:14:29.080
2018-07-06 00:00:00.000 Test1   0.95000000  2019-05-07 18:14:29.080
2018-07-13 00:00:00.000 Test1   0.00000000  2019-05-07 18:14:29.080
.
.
2019-03-22 00:00:00.000 Test1   0.95000000  2019-05-07 18:14:29.080
.
.
2018-07-06 00:00:00.000 Test4   0.00000000  2019-05-07 18:14:29.080
.
.
2019-03-22 00:00:00.000 Test4   3.20000000  2019-05-07 18:14:29.080
2019-03-22 00:00:00.000 Test4   4.00000000  2019-05-07 18:14:29.083

Gen Wan · Accepted Answer · 2019-05-07 19:52:24Z

1

You need to reset the @startval after the third loop every time.

Otherwise it will not go into the third loop anymore.

    WHILE @Startdt <= @enddt
    BEGIN
                 WHILE @startval<=@endval
                 BEGIN
                 INSERT INTO #Final
                 VALUES
                        (  @Startdt, @name, @startval, GETDATE() )

                 SET @startval=@startval+@incval
                 END

                 select @startval = startval from #temp where id=@cnt 
                 --Above is what you need to add 
                 SET @Startdt = DATEADD(DAY,7,@Startdt)
    END

Test Result:

DB<>Fiddle

edited May 7, 2019 at 19:52

answered May 7, 2019 at 17:20

Gen Wan

2,0192 gold badges14 silver badges19 bronze badges

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

2 Comments

Idea Over a year ago

Hi, I have removed @productname and tried to use set @name='' instead of variable assigning from select statement, but it is not working. Still same issue.

Idea Over a year ago

I have added the expected result set now

Collectives™ on Stack Overflow

How to execute multiple while loops or nested while loop from parameters in SQL Server

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related