question

AfshaanShaikh-1246 avatar image
0 Votes"
AfshaanShaikh-1246 asked ErlandSommarskog commented

MSSQL Server Query with multiple while loops

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

sql-server-transact-sql
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What is the outputs?

 SET @TempStartDate = Dateadd(minute,  @extractionInterval, @tempStartDate)    
 SELECT @TempStartDate


0 Votes 0 ·

hi,

Following is the output we are getting:



84421-image.png


expected output after execution shd be as follows:

84347-image.png


0 Votes 0 ·
image.png (50.9 KiB)
image.png (51.4 KiB)

The images are very difficult to read, so I may be missing something. But I could not see any difference.

Maybe it is better to show the output as text than as an image.

But even better is that you explain from start to end what you want to achieve. The above screams for redesign. Both with regards to the handling of the timestamp and having a dynamic table name. This PlantLineMapId should not be part of the table name - it should be a column in the table.

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered

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

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



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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 @sql, N'@TempStartDate datetime', @TempStartDate

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered

Hi @AfshaanShaikh-1246

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered

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
 . . .

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.