CODEWITHSUNDEEP
sqlsql-servert-sql
user1884032
user1884032

Reputation: 347

tsql while loop and time issue

I dont know what is wrong with my query but it is not breaking out of while loop. I think it is how I am doing calculation for time but not sure.

DECLARE @start DATETIME = GETDATE(), @currenttime INT = 0

while @currenttime <= 1 --- represent min
begin

    [execute Query]

    ' this will always return 0 and keep executing query even though the 1 min has been reached
    set @currenttime = CONVERT(INT, DATEDIFF(s, @start, GETDATE())%3600/60) 
end

Upvotes: 1

Views: 1476

Answers (3)

3N1GM4
3N1GM4

Reputation: 3351

As per this answer to a similar question, if you wanted to run your script once a second for 60 seconds for example:

DECLARE @i INT = 1;

WHILE (@i <= 60)
 BEGIN
  WAITFOR DELAY '00:00:01'

       /*Your Script*/

 SET  @i = @i + 1;
END

Upvotes: 0

apc
apc

Reputation: 5566

Instead try calculating the end time and looping until it is reached

DECLARE @EndTime DATETIME
SET @EndTime = DATEADD(SECOND, 5, GETDATE())

WHILE GETDATE() < @EndTime
BEGIN

    PRINT CAST(DATEDIFF(SECOND, GETDATE(), @EndTime) AS VARCHAR(5)) + ' Seconds Remaining' 

END

Upvotes: 3

Zohar Peled
Zohar Peled

Reputation: 82474

Another way to do it is to change the condition of the while loop:

SELECT @Start = GETDATE()

WHILE DATEDIFF(SECOND, @Start, GETDATE()) < 3 -- Execute the loop for 3 seconds
BEGIN

SELECT 1

END

Upvotes: 5

Related Questions