MSSQL Server Query with multiple while loops

Afshaan Shaikh 1 Reputation point
2021-04-01T14:05:59.743+00:00

Hi,

We are using the below query to iterate through all the records and then increment a date as follows:

 WHILE ( @TempStartDate <= @endDateTime )
    BEGIN
        WHILE (@RowNo < = @Tot_Count)
        BEGIN
        Print @TempStartDate
        SET @DSQL = 'SELECT F1 FROM Evaluation.CalculatedLimits_' + CAST(@PlantLineMapId as varchar) + ' WHERE PROCESS = 2 AND TIMESTAMP = '  +  ''''+CONVERT(nvarchar(50), @TempStartDate,121)+'''' 
        SET @RowNo = @RowNo + 1
        END
   SET @TempStartDate = Dateadd(minute,  @extractionInterval, @tempStartDate)   
   Print @DSQL
   END

the @tempStartDate variable is not parsing correctly in the 2nd loop which is not allowing to increment the date by 10 minutes.
Please let me know whats going wrong or did i miss any step.

Any help would be very much appreciated.

Regards,
Afshaan Shaikh

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Viorel 110.8K Reputation points
    2021-04-01T19:33:12.043+00:00

    Probably you should add something like 'SET @RowNo = 1' after the first BEGIN.

    And it is not clear how the intermediate values of @afjv are used.

    0 comments No comments

  2. Erland Sommarskog 100.1K Reputation points MVP
    2021-04-01T22:03:18.937+00:00

    It's impossible to understand what that code is supposed to do, and why it is written that way. Looks like there is a design problem.

    But change:

       '  
       SELECT F1 FROM Evaluation.CalculatedLimits_' + CAST(@PlantLineMapId as varchar) + ' WHERE PROCESS = 2 AND TIMESTAMP = '  +   
       '''+CONVERT(nvarchar(50), @TempStartDate,121)+''''  
    

    to

       SELECT F1 FROM Evaluation.CalculatedLimits_' + CAST(@PlantLineMapId as varchar) + ' WHERE PROCESS = 2 AND TIMESTAMP = @TempStartDate,121)'  
    

    and pass @tempStartDate as a parameter to your dynamic SQL:

       EXEC sp_executesql [@](/users/na/?userId=070292c7-7ffe-0006-0000-000000000000), N'@TempStartDate datetime', @TempStartDate  
    

    But the whole things looks like you need to back to the drawing board.

    0 comments No comments

  3. EchoLiu-MSFT 14,571 Reputation points
    2021-04-02T03:09:02.67+00:00

    Hi @Afshaan Shaikh

    Welcome to microsoft TSQL Q&A forum!

    You don't seem to set an initial value for @RowNo.The sample information you provided is too little, so that we can not fully understand your needs and actual code.

    If the experts’ suggestions cannot solve your problem, please provide a complete minimal example.

    If you have any question, please feel free to let me know.

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  4. Viorel 110.8K Reputation points
    2021-04-05T06:24:25.32+00:00

    According to new details, it seems that you should also exchange the lines:

    . . .
    END
    SET @TempStartDate = Dateadd(minute,  @extractionInterval, @tempStartDate)
    . . .
    

    to:

    . . .
    SET @TempStartDate = Dateadd(minute,  @extractionInterval, @tempStartDate)
    END
    . . .
    
    0 comments No comments