1

I am making a stored procedure, I want to make it auto executable after every 5 seconds.

I tried "WAITFOR DELAY '00:00:05';" but didn't fulfill my need. 

WAITFOR DELAY '00:00:05'

I want one output result i.e one output table, not all previous results.

Syed Fahad Shamim
  • 51
  • 6
  • 4
    (1) This sounds like a really bad idea and the wrong way to accomplish some goal. (2) Use SQL Server Agent to schedule jobs. – Gordon Linoff Sep 22 '19 at 17:50
  • 2
    Gordon's first point is by _far_ the more important one. Take a step back and think about what you're trying to accomplish. Running a stored procedure every five seconds is almost certainly not the best solution. Please read about [the XY problem](https://meta.stackexchange.com/q/66377/248627). – ChrisGPT was on strike Sep 22 '19 at 18:00
  • Certainly the others are right, this is definitely an XY Problem and if you *think* you need to run an SP that frequently then something *else* is wrong. Note, however, that the most frequently an Agent task can be scheduled for is 30 seconds. – Thom A Sep 22 '19 at 18:37
  • Using Agent, your job step can run a loop with WAITFOR DELAY, so the job step runs for a couple of minutes. The schedule it to run continuously. See eg: https://dba.stackexchange.com/questions/76802/how-to-schedule-jobs-in-sql-server-2008-for-less-than-10-sec – David Browne - Microsoft Sep 22 '19 at 19:45

2 Answers2

0

You can use sql server agent to schedule jobs to run this stored procedure. Writing some logic in code would not be a good idea, as the code execution might time-out once the execution starts taking time (amount of data grows ).

Refer this : How to run a stored procedure in sql server every hour?

vS12
  • 310
  • 2
  • 8
0

Use SQL Server Agent and create a job and schedule it to run every 5 seconds

John Meek
  • 173
  • 1
  • 9