SQL Command Question

Question

Post reply

SQL Command Question

robm1391

SSC Veteran

Points: 215
More actions
October 11, 2012 at 2:05 pm

#263860

Is there a way to run a SQl statement to auto RE RUN if the results are greater than 0?
Essentially it is checking to see if employees are all totalized so if it is = 0 then the process continues... but if greater than 0 than we want to wait say 5 minutes and have it re run the sql statement UNTIL it returns 0.
Any thoughts?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

well, it's possible... you can do it with a wHILE loop, i guess.

something like this seems to be what you are asking for, maybe?

DECLARE @rowcount int

SET @rowcount = 1

WHILE @rowcount > 0

BEGIN

EXEC pr_someprocess 'SomeParameters'

--test to see if all processing should be completed or not?

SELECT

@rowcount = COUNT(*)

FROM Employees

WHERE Something = SomeCriteria

--pause for 5 minites, take a deep breath and keep processing.

WAITFOR DELAY '05:00';

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

robert.gerald.taylor SSChampion Points: 13133 More actions · Answer 2

robm1391 (10/11/2012)
Is there a way to run a SQl statement to auto RE RUN if the results are greater than 0?
Essentially it is checking to see if employees are all totalized so if it is = 0 then the process continues... but if greater than 0 than we want to wait say 5 minutes and have it re run the sql statement UNTIL it returns 0.
Any thoughts?

You could set up a SQL Agent job scheduled to run every five minutes. Have the first step be a T-SQL statement do a count. If the count is > 0 then raise an error. Have the first step of the SQL Agent job quit the job on failure. If the count = 0, no error raised, step 1 suceeds and then proceeds to step 2 of the SQL Agent job.

Just an idea,

Rob

robm1391 SSC Veteran Points: 215 More actions · Answer 3

I will try that... Thanks!!!!! something to start with for sure...