SQL | WHILE LOOP w/ Time Delay

August 21, 2009

I’m working with a situation that a production database was populating a notification table for a downstream process which would delete the data post-process. Here’s what would happen. . .Once a set of application rules inserted the formatted variables into respective fields a downstream process will retrieve the data, deliver notifications, and then re-query the database for that row and delete it.

So if you’re trying to retrieve the row(s) prior to the down stream process deleting it – you can use the following logic to continue looking until it finds data in the table. I did this to copy a production data row and then insert the results into a test/dev environment. This approach helped avoid impact of production processes to force data output.

NOTE : I initially built this without a timing mechanism so it would continually run until one or more rows returned – then a select statement follows to a specific set of fields from the table. To help matters, I introduced a delay in the loop, to avoid maxing out a CPU on the SQL Server, at a (5) second interval. . .

declare @count int
SET @count = 0

WHILE @count = 0
BEGIN
 set @count = (Select count(*) from [table name])
 waitfor delay ‘0:0:5’
END

Select * from [table name]

Thanx – J

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: