SQL Server Execute Query Every Interval

Question

I would like to execute a query in the Management Studio every 3 seconds for monitoring/maintenance needs. I know I can define a job or use an external application. But I 'm looking for something much simpler. Something like the following pseudo:

DECLARE @Interval INT
SET @Interval = 3000 -- I'm expecting milliseconds
BEGINLOOP (@Interval)
    SELECT * FROM MyTransactions
ENDLOOP

I would like the output to refresh every 3 seconds. Is that possible?

Answer 1

You could use WAITFOR in a loop. You would specify the interval time in the WAITFOR statement.

Something like this:

WHILE 1=1
BEGIN
   WAITFOR DELAY '00:00:05' -- Wait 5 seconds

   SELECT * FROM MyTransactions

   -- Break on some condition
END

Answer 2

DECLARE @Interval INT;
SET @Interval = 3000;

DECLARE @Delay DATETIME;
SET @Delay = DATEADD(MILLISECOND, @Interval, 0);

DECLARE @i INT;
SET @i = 1;
WHILE @i <= 1000
BEGIN
    WAITFOR DELAY @Delay;

    PRINT @i; -- Do something

    SET @i = @i + 1;
END