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.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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
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.
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.
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
. . .